Max(date) as flag in load

[Anonymous]

Hi,

I have a simple data with a date field. I want to create a new field, "Maxflag" in the table where if the Date is the max(Date) group by ID then Maxflag=Y.

I don't want to just load the maximum rows. I want to load everything but with a field ("Maxflag") that identifies which row is has the max date for each ID.

DATA:

LOAD * INLINE [

    ID, Date

    1, 1/1/2018

    1, 3/1/2018

    2, 9/1/2019

    2, 3/1/2018

    3, 5/1/2018

    3, 4/1/2017

];

Intended table should be:

ID	Date	Maxflag
1	1/1/2018
1	3/1/2018	Y
2	9/1/2019	Y
2	3/1/2018
3	5/1/2018	Y
3	4/1/2017

[passionate]

PFA, Solution

[sasiparupudi1 · Accepted solution]

Another Solution..

Data:

LOAD ID,

     Date

FROM

[https://community.qlik.com/thread/298279]

(html, codepage is 1252, embedded labels, table is @1);

Left join

LOAD ID,

     Max(Date) as Date,

     'Y' as MaxFlag

FROM

[https://community.qlik.com/thread/298279]

(html, codepage is 1252, embedded labels, table is @1)

Group by ID;

[MarcoWedel]

Hi,

another solution could be:

tabDATATemp:
LOAD ID,
    Date
FROM [https://community.qlik.com/thread/298279] (html, codepage is 1252, embedded labels, table is @1);

tabDATA:
LOAD *,
    If(ID<>Previous(ID),'Y') as MaxFlag
Resident tabDATATemp
Order By ID, Date desc;

DROP Table tabDATATemp;

hope this helps

regards

Marco