Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
This should be straight forward but I cannot think of the solution (without joins):
I have a table with 3 columns Field A, DATE and VALUE
I would like to create two more fields: VALUE_2 and VALUE_3
VALUE_2 = the 2nd value of each field value of Field A, i.e. 2nd value of nyc, 2nd value of hk
VALUE_3 = the 3rd value of each field value of Field A,, i.e. 3rd value of nyc, 3rd value of hk
FIELD A | DATE | VALUE | VALUE_2 | VALUE_3 |
nyc | 01/01/2020 | Y | N | Y |
nyc | 01/02/2020 | N | N | Y |
nyc | 01/03/2020 | Y | N | Y |
hk | 01/01/2020 | Y | Y | N |
hk | 01/02/2020 | Y | Y | N |
hk | 01/03/2020 | N | Y | N |
without doing joins is there a function to do this?
Thanks
One solution:
t1:
Load FIELD_A, DATE, VALUE,
FIELD_A&Autonumber(RowNo(), FIELD_A) as FieldCounter,
Autonumber(RowNo(), FIELD_A) as Counter
Inline [
FIELD_A DATE VALUE
nyc 01/01/2020 Y
nyc 01/02/2020 N
nyc 01/03/2020 Y
hk 01/01/2020 Y
hk 01/02/2020 Y
hk 01/03/2020 N] (delimiter is spaces);
t2:
Load
*,
If(Counter=1 or Peek(FIELD_A)=FIELD_A , Lookup('VALUE', 'FieldCounter',FIELD_A&2, 't1')) as Value2,
If(Counter=1 or Peek(FIELD_A)=FIELD_A , Lookup('VALUE', 'FieldCounter',FIELD_A&3, 't1')) as Value3
Resident t1 ;
Drop table t1;
Thanks for the response, but realised I could use firstsortedvalue as it is sorted by date (month).
One solution:
t1:
Load FIELD_A, DATE, VALUE,
FIELD_A&Autonumber(RowNo(), FIELD_A) as FieldCounter,
Autonumber(RowNo(), FIELD_A) as Counter
Inline [
FIELD_A DATE VALUE
nyc 01/01/2020 Y
nyc 01/02/2020 N
nyc 01/03/2020 Y
hk 01/01/2020 Y
hk 01/02/2020 Y
hk 01/03/2020 N] (delimiter is spaces);
t2:
Load
*,
If(Counter=1 or Peek(FIELD_A)=FIELD_A , Lookup('VALUE', 'FieldCounter',FIELD_A&2, 't1')) as Value2,
If(Counter=1 or Peek(FIELD_A)=FIELD_A , Lookup('VALUE', 'FieldCounter',FIELD_A&3, 't1')) as Value3
Resident t1 ;
Drop table t1;
Thanks for the response, but realised I could use firstsortedvalue as it is sorted by date (month).