Get Max Date For Each id

[Anonymous]

Hi

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

[Anonymous]

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

[Nicole-Smith · Accepted solution]

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.

[Anonymous]

Worked like a charm . Thank you