Creating a series column based on other column values.

Question

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 1Column 2No1No1No1No1Yes2No2No2No2No2No2Yes3No3No3No3

jthi · Answer

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

ileshem · Answer

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

Column 0Column 1SeriesRedNo1RedNo1RedNo1RedNo1RedYes2RedNo2RedNo2BlueNo1BlueNo1BlueNo1BlueYes2BlueNo2BlueNo2BlueNo2GreenNo1GreenNo1GreenNo1GreenNo1GreenNo1GreenYes2GreenNo2GreenNo2

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

jthi · Answer

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