topic Re: Max Date - Expression or Script? in QlikView

Max Date - Expression or Script?

darren_dixon — Fri, 24 Nov 2017 11:38:39 GMT

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 — Fri, 24 Nov 2017 11:45:53 GMT

try

firstsortedvalue(Transactiondate,-Value)

avkeep01 — Fri, 24 Nov 2017 11:50:47 GMT

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 — Fri, 24 Nov 2017 15:22:24 GMT

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 — Fri, 24 Nov 2017 16:21:28 GMT

May be this