Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Black_Hole
Creator II
Creator II

FirstSortedValue with two criteria

Hello all, 

I would like to use the function FirstSortedValued by considering two criteria inside. 

I try to affect the "ChargesFees" value to the "NetAmountConverted" for only the row which has the oldest date and time.

For example:

RowNo | InvoiceNumber  |  NetAmountConverted  |  ChargesFees  |  EntryDate           |  EntryTime

1 | INV01                         1000                                    |   60                      20200123 |  1501

2 |INV01                         2000                                    |   60                      20200123 |  1502

3 |INV02                         5000                                    |   12                      20191208 |  1620

4 |INV02                         100                                      |   12                      20200706 |  0935

 

> The result expected:

RowNo | InvoiceNumber  |  TempValue    

1 |INV01                         1060                                               

2 |INV01                         2000                                                

3 |INV02                         5012                                                

4 |INV02                         100                                                   

 

> My current script:

NoConcatenate
[RESULT_TEMP]:
Load Distinct
InvoiceNumber,
FirstSortedValue(SUM(NetAmountConverted)+SUM(ChargesFees), -EntryDate and -EntryTime) AS TempVlaue
Resident INV_TEMP
Group By InvoiceNumber;

Please could you highlight me what is not correct in my current script.

Thank you in advance for your help.

 

Labels (2)
1 Solution

Accepted Solutions
Black_Hole
Creator II
Creator II
Author

Finally, I resolved my issue. Below the script:

NoConcatenate
[RESULT_TEMP]:
Load Distinct
InvoiceNumber,
FirstSortedValue(DISTINCT NetAmountConverted, -EntryDate and -EntryTime) + SUM(ChargesFees) AS TempVlaue
Resident INV_TEMP
Group By InvoiceNumber;

View solution in original post

1 Reply
Black_Hole
Creator II
Creator II
Author

Finally, I resolved my issue. Below the script:

NoConcatenate
[RESULT_TEMP]:
Load Distinct
InvoiceNumber,
FirstSortedValue(DISTINCT NetAmountConverted, -EntryDate and -EntryTime) + SUM(ChargesFees) AS TempVlaue
Resident INV_TEMP
Group By InvoiceNumber;