Unexpected error during script

[#1]

Hello,

I'm writing a script to deconvolute a json file.

Once in jmp, the resulting table is composed of groups of columns  labelled such as study.xyz, study.cro, groups.xyz, groups.id, test.xyz, test.name,...

I'm trying to create a series of tables for each of the groups such as study/groups/test.

I have a script that does that well when each subset is handle separately but for some reasons it stops creating the subset table when it moves to the next group.

I can move around the order in which those groups are processed and the first group to be 'subseted' for well.

In other words, if I try to subset 'study' first, then 'groups' is not processed

If I move the script portion dedicated to 'groups' in first position, it get processed  but then 'study' does not.

I'm baffled. Any suggestions would be greatly appreciated.

Best,

Sebastien

		try(data table("P0_json")<<close window);
				try(data table("study")<<close window);
				try(data table("groups")<<close window);
		New Table( "P0_json",
	Add Rows( 16 ),

	New Column( "groups.group_id",
		Character,
		"Nominal",
		Set Values(
			{"A", "B", "C", "D", "E", "F", "G", "H", "A", "B", "C", "D", "E", "F",
			"G", "H"}
		)
	),
	New Column( "groups.strain",
		Character,
		"Nominal",
		Set Values(
			{"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6",
			"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6",
			"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6"}
		)
	),
	New Column( "groups.study_id",
		Character,
		"Nominal",
		Set Values(
			{"ELN-19-0587", "ELN-19-0587", "ELN-19-0587", "ELN-19-0587",
			"ELN-19-0587", "ELN-19-0587", "ELN-19-0587", "ELN-19-0587",
			"ELN-19-0588", "ELN-19-0588", "ELN-19-0588", "ELN-19-0588",
			"ELN-19-0588", "ELN-19-0588", "ELN-19-0588", "ELN-19-0588"}
		)
	),
	New Column( "study.investigator_study_id",
		Character,
		"Nominal",
		Set Values(
			{"NT200109", "NT191209", "", "", "", "", "", "", "", "", "", "", "", "",
			"", ""}
		)
	),
	New Column( "study.cro",
		Character,
		"Nominal",
		Set Values(
			{"XYZ", "XYZ", "", "", "", "", "", "", "", "", "", "", "", "", "",
			""}
		)
	)
);
//create study subset	
json =  Data Table("P0_json");
json<< clear column selection();
json << select all rows;
			
tab = "study.";
nm="study";
Col_List = json << Get Column Names( "String" );

For( i = 1, i <= N Items( Col_List ), i++,
	If( Contains( Col_List[i], tab ),
		Column( Col_List[i] ) << Set Selected( 1 )
	)
);		

json<< Subset(
	Output Table( nm ),
	Selected Rows( 1 ),
	Selected columns only( 1 )
);
Data Table( nm ) << select where( :study.cro == "" ) << delete rows;

col = Data Table( nm )  << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], tab ),
	show(col[i]);
		column(col[i]) << set name(munger(col[i],1,tab,""))
	)
);		

//create group subset		
//json =  Data Table("P0_json");		
json<< clear column selection();
json << select all rows;
tab = "groups.";
nm="groups";
//Col_list = json << Get Column Names( "String" );

For( i = 1, i <= N Items( Col_List ), i++,
	If( Contains( Col_List[i], tab ),
		Column( Col_List[i] ) << Set Selected( 1 )
	)
);

json<< Subset(
	Output Table( nm ),
	Selected Rows( 1 ),
	Selected columns only( 1 )
);
Data Table( tab ) << select where( :groups.group_id == "" ) << delete rows;

col = Data Table( nm )  << get column names( string );
nc = N Items( col);
For( i = 1, i <= nc, i++,
	If( Contains( col[i], tab ),
	show(col[i]);
		column(col[i]) << set name(munger(col[i],1,tab,""))
	)
);

Here's the scripts:

[#2 · Accepted solution]

Hi,

I took a bit of time to debug but the main problem is that you were not pointing to the original table after the first subset. Also, you referred to column using your Col_List variable which can create odd results: I replace those calls with the more standard "Column (<data table>, <Col Num>)".

Here is the updated script

Names default to Here (1);

try(data table("P0_json")<<close window);
				try(data table("study")<<close window);
				try(data table("groups")<<close window);
		New Table( "P0_json",
	Add Rows( 16 ),

	New Column( "groups.group_id",
		Character,
		"Nominal",
		Set Values(
			{"A", "B", "C", "D", "E", "F", "G", "H", "A", "B", "C", "D", "E", "F",
			"G", "H"}
		)
	),
	New Column( "groups.strain",
		Character,
		"Nominal",
		Set Values(
			{"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6",
			"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6",
			"C57BL/6", "C57BL/6", "C57BL/6", "C57BL/6"}
		)
	),
	New Column( "groups.study_id",
		Character,
		"Nominal",
		Set Values(
			{"ELN-19-0587", "ELN-19-0587", "ELN-19-0587", "ELN-19-0587",
			"ELN-19-0587", "ELN-19-0587", "ELN-19-0587", "ELN-19-0587",
			"ELN-19-0588", "ELN-19-0588", "ELN-19-0588", "ELN-19-0588",
			"ELN-19-0588", "ELN-19-0588", "ELN-19-0588", "ELN-19-0588"}
		)
	),
	New Column( "study.investigator_study_id",
		Character,
		"Nominal",
		Set Values(
			{"NT200109", "NT191209", "", "", "", "", "", "", "", "", "", "", "", "",
			"", ""}
		)
	),
	New Column( "study.cro",
		Character,
		"Nominal",
		Set Values(
			{"XYZ", "XYZ", "", "", "", "", "", "", "", "", "", "", "", "", "",
			""}
		)
	)
);
//create study subset	
json =  Data Table("P0_json");
json<< clear column selection();
json << select all rows;
			
tab = "study.";
nm="study";
Col_List = json << Get Column Names( "String" );

For( i = 1, i <= N Items( Col_List ), i++,
	If( Contains( Col_List[i], tab ),
		Column( json, i ) << Set Selected( 1 ) //Changed the column call to point directly at json
	)
);		

json<< Subset(
	Output Table( nm ),
	Selected Rows( 1 ),
	Selected columns only( 1 )
);

dts = Data Table (nm);   // Summary table is now a variable called dts
dts << select where( :study.cro == "" ) << delete rows;

col = dts  << get column names( string );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], tab ),
	show(col[i]);
		column(dts, i) << set name(munger(col[i],1,tab,"")) // Changed the column call to point to nm
	)
);		

//create group subset		
	
json << clear column selection();
json << select all rows;
tab = "groups.";
nm="groups";


For( i = 1, i <= N Items( Col_List ), i++,
	If( Contains( Col_List[i], tab ),
		Column( json, i ) << Set Selected( 1 ) //Changed the column call to point directly at json
	)
);

json<< Subset(
	Output Table( nm ),
	Selected Rows( 1 ),
	Selected columns only( 1 )
);

dts = Data Table (nm); // Summary table is now a variable called dts
dts << select where( :groups.group_id == "" ) << delete rows; // changed "tab" with "nm"

col = dts  << get column names( string );
nc = N Items( col);
For( i = 1, i <= nc, i++,
	If( Contains( col[i], tab ),
	show(col[i]);
		column(dts, i) << set name(munger(col[i],1,tab,"")) // Changed the column call to point to nm
	)
);

[#3]

Hi again,

There are a few Comments that are no longer meaningful, please disregard.

Best,

TS

[#4]

Fantastic. Thanks for the feedback. I had not considered that approach.

Best,

Sebastien

[#5]

@Thierry_S has already answered your original question. Here's a different answer that might avoid writing any JSL.

You can use the JSON Wizard to get the tables you need, assuming your data is similar to this:

One way to represent your data in JSON

Open in the wizard:

Guess and stack to get the first view

Remember to stack!

Choose what JSON tags generate rows in the JMP table

Explanation: The wizard reads the JSON text, scanning for tags. Tags checked as row cause a row to be added to the table. Tags checked as col add columns.

Node shows the nesting of the text in the JSON.

Select the columns you don't want (easy on the column headers at the bottom) then pick No Column

You can select columns in the table preview, or do them one at a time in the GUI above the preview, or select them in the node tree. The No Column is applied to all selected columns.

At this point you might also uncheck Groups as a row generator. I forgot, and it made no difference this time. Depends on the JSON organization.

Click OK

The resulting table's Source script can be re-run to open the table again

Repeat for the other table

Repeat the process to get the other table. Uncheck the study row to remove a blank row from the JMP table.

The JSON I used is attached, yours is probably a little different, so the steps may be a little different. You can also rename the columns (Column Name field) but I think they are already what you want.

[#6]

This could also be a solution, but I was using the script to avoid having the user go through the wizard.

Thanks a lot for the detailed explanation,

Sebastien

[#7]

Save the script from the wizard.