Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
qspringleaf
Level III

create new column with assigned value by another column value

I have a column with date, and want to create a new column,  need assign with {A, B, C, D, E} by judging another column unique date.

Judge by "rundate" cell value(refer to below pic), kindly help on how to generate a script:

1. assign new column Cell[1] == "A", i++, if "rundate" Cell[i] = "rundate" Cell[i++], keep assign new column Cell[i++]=="A".

2. if "rundate" Cell[i] < "rundate" Cell[i++], means date time changed, need assign new column Cell[i++]=="B".

3. if "rundate" Cell[i] = "rundate" Cell[i++], keep assign new column Cell[i++]=="B".

4. if "rundate" Cell[i] < "rundate" Cell[i++], means date time changed again, need assign column Cell[i++]=="C".

 

rundate
4/1/2020 7:18
4/1/2020 7:18
4/1/2020 7:18
4/1/2020 7:47
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 9:13

 

Untitled.bmp

2 REPLIES 2
Highlighted
Thierry_S
Level VI

Re: create new column with assigned value by another column value

Hi,

 

Here is a possible solution that only uses built in functions assuming that your time values are actually coded as times (and not characters)

If( Row() == 1,
	"A",
	If( :Column 1 == Lag( :Column 1, 1 ),
		Lag( :New Column, 1 ),
		Hex To Char( Char( Num( Hex( Lag( :New Column, 1 ) ) ) + 1 ) )
	)
)

There might be more elegant ways to achieve this but I'm not aware of a function that allows for easy conversion of characters to numerical code and back

 

Best regards,

TS

Thierry R. Sornasse
Highlighted
Byron_JMP
Staff

Re: create new column with assigned value by another column value

Slightly different approach, and I'm not using letters because there are only 26, and I often have lots more categories.

 

As long at the value in your :ID (or time) column stays the same, then the counter column stays the same, but when it switches, the counter column goes up by one.

 

If( Row() == 1,
	1,
	If( :ID == Lag( :ID, 1 ),
		Lag( :counter, 1 ),
		Lag( :counter, 1 ) + 1
	)
)

These nested If statements can be really handy, or giant monsters, depending on you point of view.

Cheers,

B

JMP Systems Engineer, Pharm and BioPharm Sciences
Article Labels

    There are no labels assigned to this post.