topic JSL Sum changing columns in Discussions

JSL Sum changing columns

itzikd — Fri, 09 Jun 2023 19:45:10 GMT

Hello, if I have a table that changes once in a while something like this:

but another day it can be something like this:

and I want to somehow create a new row that will sum all the columns except for Date and Data

what is the best way to do this in JSL?

I tried taking the columns names and then removing data and date but then I'm not sure how to do the sum part for each column

I tried using summary but each time it changes, once it will be sum of a,b,r,x and once a,c,f so I'm not sure this is possible..

Summary(
	Sum( :A ),
	Sum( :B ),
	Sum( :R ),
	Sum( :X ),
	Freq( "None" ),
	Weight( "None" )
)

Summary(
	Sum( :A ),
	Sum( :C ),
	Sum( :F ),
	Freq( "None" ),
	Weight( "None" )
)

Re: JSL Sum changing columns

jthi — Sun, 25 Apr 2021 14:03:29 GMT

Maybe you could use similar idea as here:

How to create summary of a bunch of cols without specifying column names?

Names Default To Here(1);
dt1 = New Table("Untitled1",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("data", Character, "Nominal", Set Values({"a", "b", "c"})),
	New Column("date", Numeric, "Continuous", Format("Best", 12), Set Values([2021, 2021, 2021])),
	New Column("a", Numeric, "Continuous", Format("Best", 12), Set Values([0, 50, 2])),
	New Column("c", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 0])),
	New Column("f", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 7]))
);
colname = dt1 << get column names(string);
colname = Substr(colname, 3); //remove data and date columns from list
summary_dt1 = dt1 << Summary(Sum(Eval(colname)), Freq("None"), Weight("None"));
summary_dt1 << Delete Columns("N Rows");

dt2 = New Table("Untitled2",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("data", Character, "Nominal", Set Values({"a", "b", "c"})),
	New Column("date", Numeric, "Continuous", Format("Best", 12), Set Values([2021, 2021, 2021])),
	New Column("a", Numeric, "Continuous", Format("Best", 12), Set Values([0, 50, 2])),
	New Column("b", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 0])),
	New Column("r", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 7])),
	New Column("x", Numeric, "Continuous", Format("Best", 12), Set Values([2, 7, 1]))
);
colname = dt2 << get column names(string);
colname = Substr(colname, 3); //remove data and date columns from list
summary_dt2 = dt2 << Summary(Sum(Eval(colname)), Freq("None"), Weight("None"));
summary_dt2 << Delete Columns("N Rows");

Re: JSL Sum changing columns

txnelson — Sun, 25 Apr 2021 18:05:09 GMT

@jthi has provided the correct answer. I have a slight modification that I find very useful in many of the scripts that I write.

I find that most of the time, the data tables that I get to work with, do not follow the same order of the columns from one access to lets say, the following weeks access. Therefore, when I need to pair down a list of columns to be summarized etc. I use the following code.

colNamesNotToUse = {"DATA", "DATE"};
colname = dt << get column names( string );

For( i = N Items( colName ), i >= 1, i--,
	If( Contains( colNamesNotToUse, Uppercase( colName[i] ) ),
		Remove From( colName, i, 1 )
	)
);

Re: JSL Sum changing columns

ih — Tue, 27 Apr 2021 19:04:19 GMT

Another way to do the same thing. Not better, just different:

Names Default To Here(1);

dt1 = New Table("Untitled1",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("data", Character, "Nominal", Set Values({"a", "b", "c"})),
	New Column("date", Numeric, "Continuous", Format("Best", 12), Set Values([2021, 2021, 2021])),
	New Column("a", Numeric, "Continuous", Format("Best", 12), Set Values([0, 50, 2])),
	New Column("c", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 0])),
	New Column("f", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 7]))
);

dt2 = New Table("Untitled2",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("data", Character, "Nominal", Set Values({"a", "b", "c"})),
	New Column("date", Numeric, "Continuous", Format("Best", 12), Set Values([2021, 2021, 2021])),
	New Column("a", Numeric, "Continuous", Format("Best", 12), Set Values([0, 50, 2])),
	New Column("b", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 0])),
	New Column("r", Numeric, "Continuous", Format("Best", 12), Set Values([0, 0, 7])),
	New Column("x", Numeric, "Continuous", Format("Best", 12), Set Values([2, 7, 1]))
);

//Put all in one table
dtConcat = dt1 << Concatenate(
	dt2,
	Create source column
);

//Unpivot - one column with all values
dtStacked = dtConcat << Stack(
	columns( 4::n col(dtConcat) ),
	Source Label Column( "Column" ),
	Stacked Data Column( "Value" )
);

//Use tabulate to find sums
dtSum = (tab = dtStacked << Tabulate(
	Add Table(
		Column Table( Grouping Columns( :Column ), Analysis Columns( :Value ) )
	)
)) << Make Into Data Table;

//Clean Up
tab << Close Window;
dtStacked << Close Window; dtConcat << Close Window;
dt1 << Close Window; dt2 << Close Window;