256693_en-US

liliconcom

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

 

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

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