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. ItemIDaim_lio001aim_lio002aim_lio003aim_lio004aim_lio005aim_lio006aim_lio007aim_lio008aim_lio009aim_lio010aim_lio011yeah_hoo012yeah_hoo013yeah_hoo014yeah_hoo015yeah_hoo016yeah_hoo017yeah_hoo018yeah_hoo019CC_act020CC_act021CC_act022CC_act023CC_act024CC_act025CC_act026CC_act027bb_yoon001bb_yoon002bb_yoon003bb_yoon004bb_yoon005bb_yoon006bb_yoon007bb_yoon008bb_yoon009bb_yoon010bb_yoon011bb_yoon012bb_yoon013bb_yoon014lzh_hao015lzh_hao016lzh_hao017lzh_hao018Ke_oh019Ke_oh020Ke_oh021Ke_oh022Ke_oh023Ke_oh024he_low025he_low026he_low027ge_green001ge_green002ge_green003ge_green004Ye_kio005Ye_kio006

Most likely there are better ways to do this but this came quickly to my mind: Current Data Table() 99, char(tempRank), "0" || char(tempRank), ); )); I also attached data table which has the column added

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

qspringleaf

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

Accepted answers

jthi

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

403961_id_column.jmp

All comments

jthi

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

403961_id_column.jmp

txnelson

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

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

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

Quick Links