Issue with first Sorted Value

saniyask — Sat, 16 Nov 2024 00:07:30 GMT

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

Re: Issue with first Sorted Value

MayilVahanan — Tue, 25 May 2021 09:53:31 GMT

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:

Re: Issue with first Sorted Value

saniyask — Tue, 25 May 2021 12:07:49 GMT

thanks 😊

topic Re: Issue with first Sorted Value in Management & Governance

Issue with first Sorted Value

Re: Issue with first Sorted Value

Re: Issue with first Sorted Value