Qlik Community

Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saniyask
Creator
Creator

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

 

Labels (1)
  • Other

1 Solution

Accepted Solutions
MayilVahanan

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:

MayilVahanan_0-1621936405296.png

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

2 Replies
MayilVahanan

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:

MayilVahanan_0-1621936405296.png

 

Thanks & Regards,
Mayil Vahanan R
saniyask
Creator
Creator
Author

thanks 😊