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
I have not done performance testing, but if you are comparing it to flag in script? I doubt that this will be better. But comparing it to another FirstSortedValue based on a single field, I don't think this would be so bad.
Hi Sunny,
Thanks again for your help. It worked perfectly !!
Xavier
Hi,
Maybe one last question, how can I create based on the same table a new dimmension with all my NAME's?
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 | Mike | 10/03/2016 |
2 | 30 | Tom | 10/03/2016 |
2 | 40 | David | 10/03/2016 |
Result is:
ID | NUM | NAME | DATE | NEW DIMENSION |
1 | 30 | Tom | 14/03/2016 | John/Mike/Tom |
2 | 40 | David | 10/03/2016 | John/Mike/Tom/David |
Thanks for your help
This may be:
Table:
LOAD * INLINE [
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, Mike, 10/03/2016
2, 30, Tom, 10/03/2016
2, 40, David, 10/03/2016
];
Left Join (Table)
LOAD ID,
Concat(NAME, '/') as [NEW DIMENSION]
Resident Table
Group By ID;
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;
One more version..
Data:
Load ID, Max(NUM) As NUM,
FirstSortedValue(NAME,-NUM) As NAME,
FirstSortedValue(DATE,-NUM) As DATE,
Concat(NAME, '/') as [NEW DIMENSION]
Group By ID
;
LOAD * INLINE [
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, Mike, 10/03/2016
2, 30, Tom, 10/03/2016
2, 40, David, 10/03/2016
]
;
Hi Sunny,
Once again it worked! thanks for you help