topic Re: combine actions/scripts in Discussions

combine actions/scripts

joann — Fri, 05 Oct 2018 21:03:11 GMT

Hi,

I'd like to do a tabulate, make into data table, and then add calculated metric based on the data table. Below is what I have now (copying script of each action). I'm not sure how to combine the action of data table and the new metric. Please help, thanks in advance!

dt = Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				:Name( "201733 Weekly COMP Store Sales" ),
				:Name( "201833 Weekly COMP Store Sales" )
			)
		),
		Row Table(
			Grouping Columns( :Name( "18 IRI Market" ) ),
			Add Aggregate Statistics( :Name( "18 IRI Market" ) )
		)
	)
);
 
dt << makeintodatatable;
 
 
dt << New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula( :Name( "Sum(201833 Weekly COMP Store Sales)" ) / :Name( "Sum(201733 Weekly COMP Store Sales)" ) ),
	Set Selected
);

Re: combine actions/scripts

txnelson — Fri, 05 Oct 2018 21:09:27 GMT

See my comments in my changes to your script below

dt = current data table();

// dt normally is a reference to a data table, as I have referenced
// in the statement above.  The variable "tab", below, will refer to the
// tabulate platform 
tab = dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				:Name( "201733 Weekly COMP Store Sales" ),
				:Name( "201833 Weekly COMP Store Sales" )
			)
		),
		Row Table(
			Grouping Columns( :Name( "18 IRI Market" ) ),
			Add Aggregate Statistics( :Name( "18 IRI Market" ) )
		)
	)
);

// the dt2 variable will point to the new output data table
dt2 = tab << makeintodatatable; 
 
// I am a little confused as to what data table you really want to 
// add the new column to......I am changing the dt data tablereference 
// to dt2, it is in the output data table that thes columns exist
dt2 << New Column( "ratio",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		:Name( "Sum(201833 Weekly COMP Store Sales)" ) /
		:Name( "Sum(201733 Weekly COMP Store Sales)" )
	),
	Set Selected
);

Re: combine actions/scripts

joann — Fri, 05 Oct 2018 21:49:36 GMT

Thank you so much Jim!
Yes this is exactly what I need. Adding calculated metric on top of the new data table generated by tabulate. Thanks a lot!

Re: combine actions/scripts

joann — Fri, 05 Oct 2018 22:08:24 GMT

Quick question: is there a Groupby function in JSL (like in python) to replace the tabulate function?

Re: combine actions/scripts

txnelson — Sat, 06 Oct 2018 00:18:00 GMT

Try Summarize(). Also, you can add an "Invisible" parameter to the Tabulate Platform, so you never see the actual display....