Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bc5891
Contributor III
Contributor III

finding nth value of each distinct fieldvalue in script

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 ADATEVALUEVALUE_2VALUE_3
nyc01/01/2020YNY
nyc01/02/2020NNY
nyc01/03/2020YNY
hk01/01/2020YYN
hk01/02/2020YYN
hk01/03/2020NYN

 

without doing joins is there a function to do this?

 

Thanks

Labels (1)
2 Solutions

Accepted Solutions
tresesco
MVP
MVP

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;

tresesco_0-1595333896823.png

 

View solution in original post

bc5891
Contributor III
Contributor III
Author

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

View solution in original post

2 Replies
tresesco
MVP
MVP

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;

tresesco_0-1595333896823.png

 

bc5891
Contributor III
Contributor III
Author

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