topic Re: Creating a series column based on other column values. in Discussions

Creating a series column based on other column values.

ileshem — Sun, 11 Jun 2023 11:49:11 GMT

Hello, I need help with creating a column, using JSL, that fill a series and increase by one when a triggered by another column value.

Like column 2 below, every time there is a “Yes” in column 1 it increases by 1 the value in column 2.
Hope I am clear enough.

Column 1	Column 2
No	1
No	1
No	1
No	1
Yes	2
No	2
No	2
No	2
No	2
No	2
Yes	3
No	3
No	3
No	3

Re: Creating a series column based on other column values.

jthi — Fri, 09 Jun 2023 10:53:40 GMT

Below is one option using Col Cumulative Sum() with comparison

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(14),
	Compress File When Saved(1),
	New Column("Column 1",
		Character,
		"Nominal",
		Set Values(
			{"No", "No", "No", "No", "Yes", "No", "No", "No", "No", "No", "Yes",
			"No", "No", "No"}
		)
	),
	New Column("Column 2",
		Numeric,
		"Continuous",
		Format("Best", 12),
		Set Values([1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3])
	)
);

dt << New Column("Series", Numeric, Continuous, Formula(
	Col Cumulative Sum(:Column 1 == "Yes") + 1
));

Re: Creating a series column based on other column values.

ileshem — Fri, 09 Jun 2023 17:11:26 GMT

Thanks!!
Now I need some more help, if I have another column and I want it to "reset" the count, like this:

Column 0	Column 1	Series
Red	No	1
Red	No	1
Red	No	1
Red	No	1
Red	Yes	2
Red	No	2
Red	No	2
Blue	No	1
Blue	No	1
Blue	No	1
Blue	Yes	2
Blue	No	2
Blue	No	2
Blue	No	2
Green	No	1
Green	No	1
Green	No	1
Green	No	1
Green	No	1
Green	Yes	2
Green	No	2
Green	No	2

I Tried this:

dt << New Column("Series", Numeric, Continuous, Formula(If( :column 0 == Lag( :column 0, 1 ),
(Col Cumulative Sum(:column 1 == 1) + 1),
(Col Cumulative Sum(:column 1 == 1) + 0)
)));

Re: Creating a series column based on other column values.

jthi — Fri, 09 Jun 2023 17:20:57 GMT

Col Cumulative Sum() does support <byvar> so you can use :Column 0 as byvar and it will perform the calculation separately for each value found from :Column 0