finding nth value of each distinct fieldvalue in script

[bc5891]

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

[tresesco · Accepted solution]

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;

[bc5891 · Accepted solution]

Thanks for the response, but realised I could use firstsortedvalue as it is sorted by date (month).