Max Date - Expression or Script?

[darren_dixon]

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

[Chanty4u]

try

firstsortedvalue(Transactiondate,-Value)

[avkeep01]

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)

[Anonymous]

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];

[antoniotiman]

May be this