topic Re: Select columns from a data table based on rows from another data table. in Discussions

Select columns from a data table based on rows from another data table.

SpannerHead — Wed, 11 Dec 2024 20:00:46 GMT

I have 2 data tables and I want to use one to create a subset from the other. One table has a series of columns and the filter table has those column names of interest listed as cells (rows) on another table. I tried to use nested loops to get the subset but I get persistent errors.

Re: Select columns from a data table based on rows from another data table.

jthi — Wed, 11 Dec 2024 20:12:34 GMT

Can you provide example tables and an example of what you wish to do?

Names Default To Here(1); 

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt2 = New Table("Untitled",
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"age", "sex"}))
);

cols = dt2[0, 1];

dt_subset = dt << Subset(All rows, columns(Eval(cols)));

Re: Select columns from a data table based on rows from another data table.

shampton82 — Wed, 11 Dec 2024 21:04:31 GMT

Oh, try this! I had the same need I believe:

names default to here(1);

nw=newwindow("Select tables and column",show menu(0),
				Hlistbox(
					Panelbox("Data Table to select columns",
						lb1=listbox(get data table list ())
					),
					Panelbox("data table to select rows",
						vlistbox(
								lb2=listbox(get data table list ()),
								clb1=collistbox(),
								button box("press to get columns", dtname2=lb2 << Get Selected; names = datatable(dtname2[1]) << Get Column names; clb1 << append (names);),
								button box("press to select columns",col=clb1 << Get Selected; colist=column(datatable(dtname2[1]),col)<< GetValues(Format());
																	dtname1=lb1 << Get Selected; 
																	colqty=datatable(dtname1[1])<<get column names("string");
																	for(i=1, i<= nitems(colqty),i++,
																			for(j=1, j<=n items(colist),j++,
																				print(colqty[i],colist[j]);
																				if(colqty[i]==colist[j],
																						column(datatable(dtname1[1]),colist[j])<< Set Selected( 1 );
																						j=1;
																						break();
																					
																				);
																			
																			);
																		
																	);
																	
															
							
										)
						)
						
					)
				)
);

Re: Select columns from a data table based on rows from another data table.

SpannerHead — Thu, 12 Dec 2024 15:50:32 GMT

shampton82 thanks, script is great altogether.