cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
qspringleaf
Level III

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

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.

 

ItemID
aim_lio001
aim_lio002
aim_lio003
aim_lio004
aim_lio005
aim_lio006
aim_lio007
aim_lio008
aim_lio009
aim_lio010
aim_lio011
yeah_hoo012
yeah_hoo013
yeah_hoo014
yeah_hoo015
yeah_hoo016
yeah_hoo017
yeah_hoo018
yeah_hoo019
CC_act020
CC_act021
CC_act022
CC_act023
CC_act024
CC_act025
CC_act026
CC_act027
bb_yoon001
bb_yoon002
bb_yoon003
bb_yoon004
bb_yoon005
bb_yoon006
bb_yoon007
bb_yoon008
bb_yoon009
bb_yoon010
bb_yoon011
bb_yoon012
bb_yoon013
bb_yoon014
lzh_hao015
lzh_hao016
lzh_hao017
lzh_hao018
Ke_oh019
Ke_oh020
Ke_oh021
Ke_oh022
Ke_oh023
Ke_oh024
he_low025
he_low026
he_low027
ge_green001
ge_green002
ge_green003
ge_green004
Ye_kio005
Ye_kio006
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

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

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

-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

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

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

-Jarmo
txnelson
Super User

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

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";
Jim
ih
Super User (Alumni) ih
Super User (Alumni)

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

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:

View more...
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
				)
			)
		)
	)
)