topic JSL formula new column fill in sequence based on another column content in Discussions

JSL formula new column fill in sequence based on another column content

qspringleaf — Fri, 09 Jun 2023 19:53:20 GMT

could help to suggest how to use JSL formula to generate one new column "ID", and fill in sequence number from 001-00N.

if cell value is in list {aim_lio, yeah_hoo, CC_act}, new "ID" column auto fill in 001-00N.
if cell value is in list {bb_yoon, lzh_hao, Ke_oh, he_low}, new "ID" column auto fill, and restart from 001, 001-00N'.

if cell value is in list {ge_green, Ye_kio}, new "ID" column auto fill, and restart from 001, 001-00N''. thanks a lot.

Item	ID
aim_lio	001
aim_lio	002
aim_lio	003
aim_lio	004
aim_lio	005
aim_lio	006
aim_lio	007
aim_lio	008
aim_lio	009
aim_lio	010
aim_lio	011
yeah_hoo	012
yeah_hoo	013
yeah_hoo	014
yeah_hoo	015
yeah_hoo	016
yeah_hoo	017
yeah_hoo	018
yeah_hoo	019
CC_act	020
CC_act	021
CC_act	022
CC_act	023
CC_act	024
CC_act	025
CC_act	026
CC_act	027
bb_yoon	001
bb_yoon	002
bb_yoon	003
bb_yoon	004
bb_yoon	005
bb_yoon	006
bb_yoon	007
bb_yoon	008
bb_yoon	009
bb_yoon	010
bb_yoon	011
bb_yoon	012
bb_yoon	013
bb_yoon	014
lzh_hao	015
lzh_hao	016
lzh_hao	017
lzh_hao	018
Ke_oh	019
Ke_oh	020
Ke_oh	021
Ke_oh	022
Ke_oh	023
Ke_oh	024
he_low	025
he_low	026
he_low	027
ge_green	001
ge_green	002
ge_green	003
ge_green	004
Ye_kio	005
Ye_kio	006

Re: JSL formula new column fill in sequence based on another column content

jthi — Mon, 26 Jul 2021 14:33:42 GMT

Most likely there are better ways to do this but this came quickly to my mind:

Current Data Table() << New Column("IDformula", Formula(
	tempRank = Col Rank(1, If(Contains({"aim_lio", "yeah_hoo", "CC_act"}, :item),1,
		Contains({"bb_yoon", "lzh_hao", "Ke_oh", "he_low"}, :item), 2,
		Contains({"ge_green", "Ye_kio"}, :item), 3,
		4
	));
	If(tempRank < 10,
		"00"|| char(tempRank),
	tempRank > 99,
		char(tempRank),
		"0" || char(tempRank),
	);
	
));

I also attached data table which has the column added

Re: JSL formula new column fill in sequence based on another column content

txnelson — Mon, 26 Jul 2021 15:40:45 GMT

The only improvement that I would make, is in the zero filling of the final value. I prefer to get rid of IF() clauses and just calculate the answer.

Substr( "00", Length( Char( tempRank ) ) ) || Char( tempRank ) || "-00N";

Re: JSL formula new column fill in sequence based on another column content

ih — Mon, 26 Jul 2021 16:23:26 GMT

If one prefers to keep the ID field as numeric but display it with leading zeros, a custom format could handle the formatting:

New Column( "IDformula",
	Numeric,
	"Nominal",
	Format(
		"Custom",
		Formula(
			Repeat( "0", Maximum( 2 - Floor( Log10( value ) ), 0 ) ) || Format(
				value,
				"Fixed Dec",
				width,
				dec
			)
		),
		9,
		0
	),
	Input Format( "Format Pattern", "" ),
	Formula(
		Col Rank(
			1,
			If(
				Contains( {"aim_lio", "yeah_hoo", "CC_act"}, :Item ), 1,
				Contains( {"bb_yoon", "lzh_hao", "Ke_oh", "he_low"}, :Item ), 2,
				Contains( {"ge_green", "Ye_kio"}, :Item ), 3,
				4
			)
		)
	)
);

To reproduce the whole table:

New Table( "Untitled",
	Add Rows( 60 ),
	New Column( "Item",
		Character,
		"Nominal",
		Set Values(
			{"aim_lio", "aim_lio", "aim_lio", "aim_lio", "aim_lio", "aim_lio",
			"aim_lio", "aim_lio", "aim_lio", "aim_lio", "aim_lio", "yeah_hoo",
			"yeah_hoo", "yeah_hoo", "yeah_hoo", "yeah_hoo", "yeah_hoo", "yeah_hoo",
			"yeah_hoo", "CC_act", "CC_act", "CC_act", "CC_act", "CC_act", "CC_act",
			"CC_act", "CC_act", "bb_yoon", "bb_yoon", "bb_yoon", "bb_yoon",
			"bb_yoon", "bb_yoon", "bb_yoon", "bb_yoon", "bb_yoon", "bb_yoon",
			"bb_yoon", "bb_yoon", "bb_yoon", "bb_yoon", "lzh_hao", "lzh_hao",
			"lzh_hao", "lzh_hao", "Ke_oh", "Ke_oh", "Ke_oh", "Ke_oh", "Ke_oh",
			"Ke_oh", "he_low", "he_low", "he_low", "ge_green", "ge_green",
			"ge_green", "ge_green", "Ye_kio", "Ye_kio"}
		)
	),
	New Column( "ID",
		Numeric,
		"Continuous",
		Format( "Best", 3 ),
		Set Values(
			[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
			21, 22, 23, 24, 25, 26, 27, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
			14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 1, 2, 3, 4, 5, 6
			]
		),
		Set Display Width( 93 )
	),
	New Column( "IDformula",
		Numeric,
		"Nominal",
		Format(
			"Custom",
			Formula(
				Repeat( "0", Maximum( 2 - Floor( Log10( value ) ), 0 ) ) ||
				Format( value, "Fixed Dec", width, dec )
			),
			9,
			0
		),
		Input Format( "Format Pattern", "" ),
		Formula(
			Col Rank(
				1,
				If(
					Contains( {"aim_lio", "yeah_hoo", "CC_act"}, :Item ), 1,
					Contains( {"bb_yoon", "lzh_hao", "Ke_oh", "he_low"}, :Item ), 2,
					Contains( {"ge_green", "Ye_kio"}, :Item ), 3,
					4
				)
			)
		)
	)
)