Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Max(date) as flag in load

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:

IDDateMaxflag
11/1/2018
13/1/2018Y
29/1/2019Y
23/1/2018
35/1/2018Y
34/1/2017
1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

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;

View solution in original post

3 Replies
passionate
Specialist
Specialist

PFA, Solution

sasiparupudi1
Master III
Master III

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:

QlikCommunity_Thread_298279_Pic1.JPG

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