topic Re: How do I correctly iterate over column names to produce summary tables grouped by each? in Discussions

How do I correctly iterate over column names to produce summary tables grouped by each?

OC200m — Thu, 08 Jun 2023 16:42:39 GMT

Hi there,

I'm writing a script in jsl to search through column headers, find those beginning with the number 9, and then produce a summary table from a large data set that is grouped by those columns. Currently, my code is working for the first column, but seems to stop after that. Any help would be appreciated!

dt = Current Data Table();
N = N Col( dt );
For( i = 1, i <= N, i++,
	colname = Column( i ) << getname();
	If( Starts With( :Column( i ) << getname(), "9" ),
		Data Table( "Func Counter pull" ) << Summary(
			Group( :Column( i ) << getname() ),
			N,
			Subgroup( :INTERFACE_BIN_119325 ),
			Freq( "None" ),
			Weight( "None" )
		)
	)

	;
);

It works through the iterations when doing like the following, but doesn't actually summarise by the group:

dt = Current Data Table();
N = N Col( dt );
emp_array = {};
For( i = 1, i <= N, i++,
	colname = Column( i ) << getname();
	If( Starts With( :Column( i ) << getname(), "9" ),
		Insert Into( emp_array, :Column( i ) << getname() )
	);
);

For( m = 1, m <= N Items( emp_array ), m++,
	a = emp_array[m];
	Data Table( "Func Counter pull" ) << Summary(
		Group( :Name( "a" ) ),
		N,
		Subgroup( :INTERFACE_BIN_119325 ),
		Freq( "None" ),
		Weight( "None" )
	);
);

If someone could point out how either is going wrong , that would be much appreciated!

Re: How do I correctly iterate over column names to produce summary tables grouped by each?

txnelson — Tue, 17 Jan 2023 16:17:45 GMT

The main issue that you were running into was that the Column() function has an optional element that can be specified that points to the data table reference to set the table to get the column from. If it is not specified, it points to the currently active data table. Since the Summary platform creates a new data table, it is that new table that it points to, rather than the original one you want it to point to. The code below provides specific references.

names default to here(1);

dt = Current Data Table();
N = N Col( dt );
For( i = 1, i <= N, i++,
	colname = Column( dt, i ) << getname();
	If( Starts With( As Column( dt, i ) << getname(), "9" ),
		dt << Summary(
			Group(  colname  ),
			N,
			Subgroup( :INTERFACE_BIN_119325 ),
			Freq( "None" ),
			Weight( "None" )
		)
	);
);

Re: How do I correctly iterate over column names to produce summary tables grouped by each?

OC200m — Wed, 18 Jan 2023 08:42:29 GMT

Makes sense, thanks so much!