topic Re: finding nth value of each distinct fieldvalue in script in App Development

finding nth value of each distinct fieldvalue in script

bc5891 — Sat, 16 Nov 2024 01:50:49 GMT

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

Re: finding nth value of each distinct fieldvalue in script

tresB — Tue, 21 Jul 2020 12:18:28 GMT

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;

Re: finding nth value of each distinct fieldvalue in script

bc5891 — Tue, 21 Jul 2020 12:25:08 GMT

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