Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;