cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
Thierry_S
Super User

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
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, Health and Life Sciences (Pharma)