How to fit data into a standard list of columns?

zjuv007

I have a set of standard columns that I would like to use for multiple data tables, the problem is that some data tables may not contain ALL of those columns.

 

How can I simply:

1. Create a data table with those standard list of columns (ex: ES1, ES2, ES3, ES4, ES5)

ES1	ES2	ES3	ES4	ES5

2. Read a data table that may contain partial of the above columns (ex: ES1, ES3) 

	ES1	ES3
Count Line 1	2	4
Count Line 2	2	10

3. Create the data table of count of # 2 data table with the full list of standard columns:

	ES1	ES2	ES3	ES4	ES5
Count Line 1	2	0	4	0	0
Count Line 2	2	0	10	0	0

 

txnelson

Given your new specification, the issue becomes a little more complex.  There is not a built in summation feature in JMP.  There are just so many different actions that one may want to do with the joining of data, the summation is not set as the default item to do.  The simple script below performs the summation you want.  Most of the code shown, is just there to create the sample data tables.  Only the code in lines 32-59 do the work of putting the data together.

Names Default To Here( 1 );

// Create the sample data tables
dt = New Table( "Primary",
	Add Rows( 3 ),
	New Column( "Column 1",
		Character,
		"Nominal",
		Set Selected,
		Set Values( {"NDP1007", "NDP2024", "NDP3030"} )
	),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 1] ) ),
	New Column( "ES2", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 0, 1] ) ),
	New Column( "ES3",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [5, 0, 2] ),
		Set Display Width( 46 )
	),
	New Column( "ES4", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [3, 1, 3] ) ),
	New Column( "ES5", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [0, 2, 3] ) ),
	Set Row States( [0, 1, 0] )
);
dt2 = New Table( "Secondary",
	Add Rows( 2 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"NDP1007", "NDP2024"} ) ),
	New Column( "ES1", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 0] ) ),
	New Column( "ES3", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 3] ) )
);

// Change the names in the Secondary data table so after joining they can be summed together
// and create a list of the new names
newColNameList = {};
For( i = 2, i <= N Cols( dt2 ), i++,
	Column( dt2, i ) << set name( (Column( dt2, i ) << get name) || "_X" );
	Insert Into( NewColNameList, Column( dt2, i ) << get name );
);


// Update the primary data table with the secondary table
dt << Update( With( dt2 ), Match Columns( :Column 1 = :Column 1 ) );

// Create the code to add the data together that needs to be summed
For( col = 1, col <= N Items( newColNameList ), col++, 
    // Make sure the standard name is in the data table and if so, add
	// the values together
	If( Try( Column( dt, Word( -2, newColNameList[col], "_" ) ) << get name ) != "",
		For( theRow = 1, theRow <= N Rows( dt ), theRow++,
			Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow] = Sum(
				Column( dt, Word( -2, newColNameList[col], "_" ) )[theRow],
				Column( dt, newColNameList[col] )[theRow]
			)
		)
	)
);

// Delete the Secondary column no longer needed
dt << delete columns( newColNameList );