Choose Language Hide Translation Bar
Highlighted
joann
Level I

combine actions/scripts

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
);
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: combine actions/scripts

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
);
Jim

View solution in original post

4 REPLIES 4
Highlighted
txnelson
Super User

Re: combine actions/scripts

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
);
Jim

View solution in original post

Highlighted
joann
Level I

Re: combine actions/scripts

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!
Highlighted
joann
Level I

Re: combine actions/scripts

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

Re: combine actions/scripts

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

Jim
Article Labels

    There are no labels assigned to this post.