Splitting a data table by column id

CharlesIII

Hello, 

I have a large data table that the columns have an ID number followed by words like below, 

1-Date	1-parameter	1-something	2-something	2-Date	2-parameter	3-parameter	3-Date	3-something	3-else

 

My ultimate goal is to automate control charts with Date as the sample label. There are not uniform amounts of columns for each ID and they are not sorted by ascending date. 

 

My idea is to split the large data table into smaller ones by ID and then do control charts from those. However, I do not know how to do this split in an automated way. 

 

Thanks 

 

txnelson

Without more specifics, I can only give  an approximate direction on how to solve this.  I see 2 methods to go after what you need.  The first is to find out how many different IDs there are and place the column into the different lists for the IDs, doing some thing like this:

Names Default To Here( 1 );
dt = Current Data Table();

colNamesList = dt << get column names( string );

// Create an ID list
listOfIDs = {};

For( i = 1, i <= N Items( colNamesList ), i++,
	If( Contains( listOfIDs, Word( 1, Column( dt, colNamesList[i] ) << get name, "-" ) ) == 0, 
		// Insert into the listOfIDs, the new ID name
		Insert Into( listOfIDs, Word( 1, Column( dt, colNamesList[i] ) << get name, "-" ) );
		// Create a list to hold the column names for the new ID
		Eval( Parse( "listOfColumns_" || Char( N Items( listOfIDs ) ) || " = {};" ) );
		Eval(
			Parse(
				"insert into(listOfColumns_" || Char( N Items( listOfIDs ) ) || "," || Char(
					Column( dt, colNamesList[i] ) << get name
				) || ");"
			)
		);
	, 
		// If the ID has already been see, then just add the column name to the
		// ID list of column names
		Eval(
			Parse(
				"insert into(listOfColumns_" || Char(
					Contains( listOfIDs, Word( 1, Column( dt, colNamesList[i] ) << get name, "-" ) )
				) || "," || Char( Column( dt, colNamesList[i] ) ) << get name || ");"
			)
		)
	)
);

You can then loop through the listOfIDs and do your processing.

 

The other method would be to stack all of the columns, and then create a new column for ID, strip off the ID values from the labels, and then split the columns back into common columns and do your processing using a By statement to separate the different IDs.  Something like

Names Default To Here( 1 );
dt = Current Data Table();

colNamesList = dt << get column names( string );

// Stack the columns
dtStack = dt << Stack(
	columns( colNamesList ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Drop All Other Columns( 1 )
);

// Create the ID Column
dtStack << New Column("ID", character,formula( word(1,:label,"-")));

// Now delete the ID from the :Label column
For Each Row(
	:Label = trim(substr( :Label, contains(:Label,"-")+1));
);

// Split back to common columns
dtFinal = dtStack << Split(
	Split By( :Label ),
	Split( :Data ),
	Sort by Column Property
);

Now for my clarification.......as I stated, with your sparse details, I am making a lot of guesses on this.  Also, without a sample data table, the code I provided has not been tested, and I assume it would not work on first execution.  It is intended to show a posible direction you can take to solve the problem