
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
