Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
How can I get the max Transaction Date, Payment Method and value from the following data? Can this be done as an expression or in the script?
Data:
Tenancy Number | Transaction Date | Payment Method | Value |
---|---|---|---|
10001 | 05/11/2017 | Cash | £10.00 |
10001 | 12/11/2017 | Direct Debit | £20.00 |
10001 | 19/11/2017 | Direct Debit | £30.00 |
10002 | 05/11/2017 | Direct Debit | £40.00 |
10002 | 12/11/2017 | Cash | £50.00 |
10003 | 12/11/2017 | Cash | £60.00 |
Result:
Tenancy Number | Transaction Date | Payment Method | Value |
---|---|---|---|
10001 | 19/11/2017 | Direct Debit | £30.00 |
10002 | 12/11/2017 | Cash | £50.00 |
10003 | 12/11/2017 | Cash | £60.00 |
Thanks,
Darren
try
firstsortedvalue(Transactiondate,-Value)
Hi Darren,
In the script you could add the following lines:
LEFT JOIN (OriginalTableName) LOAD
[Tenancy Number],
DATE(MAX([Transaction Date])) AS [Transaction Date],
1 AS [Flag Last Transaction]
RESIDENT OriginalTableName
GROUP BY [Tenancy Number];
From there you have multiple solutions to work with:
- you could make a resident on the orginal table with a WHERE [Flag Last Transaction] = 1
- in the front end you could use SUM({$<[Flag Last Transaction]={1}>} Value)
If you don't care about keeping the other records you could just do this in your script.
(Assuming [Transaction Date] is a date field not a string)
Data:
Load
[Tenancy Number],
[Transaction Date],
[Payment Method],
Calue
From YourTable
inner join(Data)
Load
[Tenancy Number],
max(Transaction Date] as [Transaction Date]
Resident Date
Group By [Tenancy Number];
May be this