topic Re: Get Max Date For Each id in QlikView

Get Max Date For Each id

Anonymous — Thu, 30 Nov 2017 17:56:54 GMT

Consider the joined File containing four fields , I'm trying to get, for each ID_FACTURE , the maximum date DATE_ENCAISSEMENT .

To be sure Im being clear : lets say each ID_FACTURE has 10 occurences, i want to keep one line only with the max DATE_ENCAISSEMENT.

Any help would be much appreciated

Thanks in advance

Re: Get Max Date For Each id

Anonymous — Thu, 30 Nov 2017 19:15:31 GMT

try firstsortedvalue(distinct ID_FACTURE, -DATE_ENCAISSEMENT)

--> of course you'll have to use this in combination with group by in script or dimension(s) in frontend

Re: Get Max Date For Each id

Nicole-Smith — Thu, 30 Nov 2017 19:15:50 GMT

You could do it in the load script like this:

Data:
LOAD ID_FACTURE,
ID_ENCAISSEMENT,
DATE_ENCAISSEMENT,
MONTANT_ENCAISSEMENT
FROM Data.txt (txt, codepage is 1252, embedded labels, delimiter is '|', msq);

INNER JOIN (Data)
LOAD ID_FACTURE,
MAX(DATE_ENCAISSEMENT) AS DATE_ENCAISSEMENT
RESIDENT Data
GROUP BY ID_FACTURE;

I've also attached an example file.

Re: Get Max Date For Each id

Anonymous — Sat, 02 Dec 2017 12:05:07 GMT

Worked like a charm . Thank you