Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mrichman
Contributor II

How to group by with maxdate

Hi Community,

I have an issue with a group by. I would like to group by the following:

The maximum date of the underlying case is 2018-06-29. I want to group and add a column by the maximum date with the latest status. So in this case, the maximum date is 2018-06-29 and the latest status should be 2.

Example:

LOAD * INLINE [

    Client, Statusnr, Date1, MaxDate,

    A, 1, 2018-05-26, 2018-06-29

    A, 3, 2018-05-29, 2018-06-29

    A, 2, 2018-06-29, 2018-06-29

] (delimiter is ',');

Ideal result:

LOAD * INLINE [

    Client, Statusnr, Date1, MaxDate, LatestStatus

    A, 1, 2018-05-26, 2018-06-29, 2

    A, 3, 2018-05-29, 2018-06-29, 2

    A, 2, 2018-06-29, 2018-06-29, 2

] (delimiter is ',');

Thank you in advance

1 Solution

Accepted Solutions
daveamz01
Contributor III

Re: How to group by with maxdate

Hi,

Look at the FirstSortedValue function. In your case it will be something like this:

T:

LOAD *, 1 AS Dummy INLINE [

Client, Statusnr, Date1, MaxDate

A, 1, 2018-05-26, 2018-06-29

B, 3, 2018-05-29, 2018-06-29

C, 2, 2018-06-29, 2018-06-29

] (delimiter is ',');

LEFT JOIN (T)

LOAD Dummy,

     FirstSortedValue(Statusnr, -Date1) AS LatestStatus

RESIDENT T

GROUP BY Dummy

;

DROP FIELD Dummy;

Regards,

David

9 Replies
daveamz01
Contributor III

Re: How to group by with maxdate

Hi,

Look at the FirstSortedValue function. In your case it will be something like this:

T:

LOAD *, 1 AS Dummy INLINE [

Client, Statusnr, Date1, MaxDate

A, 1, 2018-05-26, 2018-06-29

B, 3, 2018-05-29, 2018-06-29

C, 2, 2018-06-29, 2018-06-29

] (delimiter is ',');

LEFT JOIN (T)

LOAD Dummy,

     FirstSortedValue(Statusnr, -Date1) AS LatestStatus

RESIDENT T

GROUP BY Dummy

;

DROP FIELD Dummy;

Regards,

David

Re: How to group by with maxdate

Or just this

T:

LOAD * INLINE [

    Client, Statusnr, Date1, MaxDate

    A, 1, 2018-05-26, 2018-06-29

    B, 3, 2018-05-29, 2018-06-29

    C, 2, 2018-06-29, 2018-06-29

];


Join (T)

LOAD FirstSortedValue(Statusnr, -Date1) AS LatestStatus

Resident T;

mrichman
Contributor II

Re: How to group by with maxdate

Thanks David,

What is the purpose of the Dummy? Does this minimize the datasize?

Thanks in advance.

Re: How to group by with maxdate

I don't think Dummy is doing anything... if you look at the script below... it essentially gives the same result without needing Dummy field.

mrichman
Contributor II

Re: How to group by with maxdate

Thanks Sunny! I thought it would minimize the database size. During a group by my data load time increases. Haven't found any solution yet.

Highlighted
daveamz01
Contributor III

Re: How to group by with maxdate

'Dummy' is used in this case to group the whole data set by ('FirstSoredValue' is an aggregation function) and also as a key to link the result to all corresponding values (all in this case). If you have multiple entries for each client and you want to determine the status at that level, you can use 'Client' field instead.

Regards,

David

shiveshsingh
Honored Contributor

Re: How to group by with maxdate

Plz try this

T:LOAD * INLINE [

    Client, Statusnr, Date1

   

    A, 1, 2018-05-26

   

    A, 3, 2018-05-29

   

    A, 2, 2018-06-29];

   

F:

load Client, Statusnr, Date(Date#(Date1,'YYYY-MM-DD'),'DD-MM-YYYY') as Date

Resident T;

drop table T;

inner join (F)

load  FirstSortedValue(Statusnr,-Date) as Latest_Status,date(max(Date)) as Max_Date

Resident F;

Re: How to group by with maxdate

Here is one where you don't really need aggregation

T:

LOAD Client,

Statusnr,

Date(Date1) as Date1;

LOAD * INLINE [

    Client, Statusnr, Date1

    A, 1, 2018-05-26

    B, 3, 2018-05-29

    C, 2, 2018-06-29

];


MaxDate:

LOAD Max(MaxDate) as MaxDate;

LOAD FieldValue('Date1', RecNo()) as MaxDate

AutoGenerate FieldValueCount('Date1');


LET vMaxDate = Peek('MaxDate');


Join (T)

LOAD Statusnr AS LatestStatus

Resident T

Where Date1 = $(vMaxDate);

mrichman
Contributor II

Re: How to group by with maxdate

Thanks for the explanation.