Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlikers,
I have a table and based on several conditions I want to find a unique date for each ID.
The table is:
ID | NUM | NAME | DATE |
1 | 10 | John | 01/03/2016 |
1 | 20 | Mike | 12/03/2016 |
1 | 30 | Tom | 14/03/2016 |
2 | 10 | John | 04/03/2016 |
2 | 20 | Tom | 10/03/2016 |
2 | 30 | Mike | 10/03/2016 |
2 | 40 | David | 06/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:
ID | NUM | NAME | DATE |
1 | 30 | Tom | 14/03/2016 |
2 | 30 | Mike | 10/03/2016 |
I tried a few things with GROUP BY, but the reultisn't there yet.
Thanks for your help
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;
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;
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)
I would use these expressions for 2 and 3
2) FirstSortedValue (NAME,-(DATE*10000+NUM))
3) FirstSortedValue (NUM,-(DATE*10000+NUM))
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.
Can you explain how it works? sunindia
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
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?
ek number bhai sunindia
Is it good w.r.t performance?