Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
reivax31
Partner
Partner

Find a unique max date

Hi Qlikers,

I have a table and based on several conditions I want to find a unique date for each ID.

The table is:

IDNUMNAMEDATE
110John01/03/2016
120Mike12/03/2016
130Tom14/03/2016
210John04/03/2016
220Tom10/03/2016
230Mike10/03/2016
240David06/03/2016

So basicaly the first think to do is to find the max DATE for each ID. For some ID you can have several lines with the same max DATE. In this case we look for the max NUM.

The expected result is:

IDNUMNAMEDATE
130Tom14/03/2016
230Mike10/03/2016

I tried a few things with GROUP BY, but the reultisn't there yet.

Thanks for your help

1 Solution

Accepted Solutions
sunny_talwar

May be this:

Table:

LOAD ID,

     NUM,

     NAME,

     DATE

FROM

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

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

Right Join(Table)

LOAD ID,

  Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

LOAD ID,

  Max(NUM) as NUM

Resident Table

Group By ID;

View solution in original post

15 Replies
sunny_talwar

May be this:

Table:

LOAD ID,

     NUM,

     NAME,

     DATE

FROM

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

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

Right Join(Table)

LOAD ID,

  Max(DATE) as DATE

Resident Table

Group By ID;

Right Join (Table)

LOAD ID,

  Max(NUM) as NUM

Resident Table

Group By ID;

Kushal_Chawda

On front end,

Create the straight table

Dimension:

ID

Expressions:

1) Date-

     FirstSortedValue (distinct Date,-Date)

2) Name-

     FirstSortedValue (distinct only({<Num={"$(=max(Num)"}>}Name),-Date)

3) Num -

     FirstSortedValue (distinct Sum({<Num={"$(=max(Num)"}>}Num),-Date)

sunny_talwar

I would use these expressions for 2 and 3

2) FirstSortedValue (NAME,-(DATE*10000+NUM))

3) FirstSortedValue (NUM,-(DATE*10000+NUM))

reddy-s
Master II
Master II

Hi Xavier,

Doing this, you will be able to fetch the max date for every unique ID

Check this out:

Load ID,

FirstSortedValue (distinct DATE,-DATE) resident ........;

Thanks,

Sangram Reddy.

Kushal_Chawda

Can you explain how it works? sunindia

sunny_talwar

Sure.

Same idea of trying to find the max (or min) based on another field, but in this case multiple fields. So to combine the multiple fields sorting, you can add them. Look at the image below

Capture.PNG

From just DATE we have a tie for ID 2 for NUM 20 and 30. But if I combine these two DATE*10000+NUM (More weight to the DATE and hence times 10000). Now the new number we just created will pick the right max based on DATE and NUM.

Does that make sense?

Kushal_Chawda

ek number bhai sunindia

sunny_talwar

Kushal_Chawda

Is it good w.r.t performance?