Issue with first Sorted Value

[saniyask]

Hi All,

I have a requirement where I need to create a new field based on the below logic.

Logic - for each Inv No find the max of ProcessDate and whatever is the response for that max date should be copied to all the invoice no's.

Sample data.

Inv No	Reason	TDate	ProcessDate	Amount
1	ABC	1/1/2021	01/02/21	100
1	PQR	1/2/2021	01/05/21	250
1	PQR	1/3/2021	01/10/21	130
3	-	1/4/2021	01/05/21	140
3	LMN	1/5/2021	01/10/21	560
5	UVW	1/6/2021	01/09/21	460
5	PQR	1/7/2021	01/14/21	50
6	ABC	1/8/2021	01/16/21	89
7	LMN	1/9/2021	01/12/21	78
9	XYZ	1/10/2021	01/11/21	100

I have tried to use first Sorted value in script as well as in Chart but it doesnt seem to work, can anyone explain what am I missing. FirstSortedValue(Reason,-ProcessDate)

Output Should be

Inv No	Reason	TDate	ProcessDate	Amount	New Reason
1	ABC	1/1/2021	01/02/21	100	PQR
1	PQR	1/2/2021	01/05/21	250	PQR
1	PQR	1/3/2021	01/10/21	130	PQR
3	-	1/4/2021	01/05/21	140	LMN
3	LMN	1/5/2021	01/10/21	560	LMN
5	UVW	1/6/2021	01/09/21	460	PQR
5	PQR	1/7/2021	01/14/21	50	PQR
6	ABC	1/8/2021	01/16/21	89	ABC
7	LMN	1/9/2021	01/12/21	78	LMN
9	XYZ	1/10/2021	01/11/21	100	XYZ

@sunny_talwar could you please check.

Regards,

Saniya

[MayilVahanan · Accepted solution]

Hi @saniyask 

Try like below

T1:
LOAD [Inv No],
Reason,
TDate,
ProcessDate,
Amount
FROM
[https://community.qlik.com/t5/Deployment-Management/Issue-with-first-Sorted-Value/td-p/1810296]
(html, codepage is 1252, embedded labels, table is @1);

Join
Load [Inv No], FirstSortedValue(Reason, - ProcessDate) as NewReason Resident T1 Group by [Inv No];

O/P:

[saniyask]

thanks 😊