Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following data.
CONFIRMED_AT | VLPLA | WHO |
29.11.201610:05:06 | 007.189.A | 50529357 |
29.11.201610:05:06 | 029.149.A | 50528122 |
29.11.201610:05:07 | 005.010.A.02 | 50528531 |
29.11.201610:05:07 | 034.214.A | 50528440 |
29.11.201610:05:09 | 008.210.A | 50523098 |
29.11.201610:05:10 | 003.195.A | 50522899 |
29.11.201610:05:10 | 026.203.A | 50522392 |
29.11.201610:05:10 | 035.167.A | 50529279 |
29.11.201610:05:10 | 046.131.A.11 | 50523137 |
29.11.201610:05:11 | 030.025.A.21 | 50528338 |
I want to load this data and then sort it. I need it in the following format.
I want to have the time being sorted Ascending, and because the WHO repeats, it needs to be grouped by WHO.
Example of required result.
CONFIRMED_AT | VLPLA | WHO |
14.12.201607:40:06 | 009.221.A | 51623062 |
14.12.201607:40:25 | 009.219.A | 51623062 |
14.12.201607:40:56 | 009.211.A | 51623062 |
14.12.201607:41:40 | 009.181.A | 51623062 |
14.12.201607:41:59 | 009.176.A | 51623062 |
14.12.201607:42:52 | 009.160.A | 51623062 |
14.12.201607:43:20 | 009.151.A | 51623062 |
14.12.201607:43:59 | 009.117.A | 51623062 |
14.12.201607:45:10 | 010.071.A.01 | 51623062 |
14.12.201607:45:59 | 010.070.A.01 | 51623062 |
14.12.201607:46:48 | 010.078.A.02 | 51623062 |
As can be seen above, Qlikview actually sorts it Ascending according to time, however it does not group all the WHO (OrderNumbers) together. Can someone please assist?
I have been struggling getting ORDER BY to work correctly. it seems to Ignore the Sort.
Hi , Just had to have the correct sequence in the ORDER BY and have it in a Resident. Thanks. Solved it right after posting.
PRE:
LOAD VLPLA,
right(WHO,8) as WHO,
CONFIRMED_AT
NoConcatenate
Final:
load *
Resident PRE
Order BY WHO desc,CONFIRMED_AT,VLPLA,;
drop table PRE;
Order by only works with Resident Load, can you post the script you are using?
Hi , Just had to have the correct sequence in the ORDER BY and have it in a Resident. Thanks. Solved it right after posting.
PRE:
LOAD VLPLA,
right(WHO,8) as WHO,
CONFIRMED_AT
NoConcatenate
Final:
load *
Resident PRE
Order BY WHO desc,CONFIRMED_AT,VLPLA,;
drop table PRE;