
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Issue with first Sorted Value
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
thanks 😊
