topic Re: create new column with assigned value by another column value in Discussions

create new column with assigned value by another column value

qspringleaf — Wed, 08 Apr 2020 08:12:04 GMT

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

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

Thierry_S — Thu, 09 Apr 2020 05:32:02 GMT

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,

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

Byron_JMP — Mon, 13 Apr 2020 16:53:43 GMT

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,