Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have two tables which contain linked information but have been asked to present in a fashion which I am struggling with. Below are the two tables and what I am being asked for.
Any advice or guidance would be greatly appreciated.
#1 Flight Records
ID | Flight Number | STD |
---|---|---|
1 | AA001 | 09:00 |
2 | AA222 | 09:30 |
3 | AA049 | 09:45 |
4 | AA012 | 10:00 |
#2 Delay Table
ID | Delay Code | Delay Time |
---|---|---|
1 | 93 | 00:10 |
1 | 85 | 00:05 |
1 | 13 | 00:01 |
2 | 93 | 00:23 |
2 | 5 | 00:10 |
3 | 8 | 00:01 |
4 | 93 | 00:10 |
4 | 10 | 00:02 |
4 | 19 | 00:05 |
4 | 21 | 00:14 |
And this is what we would like to get as an output ideally from the load script:
ID | Flight Number | STD | Delay Code 1 | Delay Code 1 Time | Delay Code 2 | Delay Code 2 Time | Delay Code 3 | Delay Code 3 Time | Delay Code 4 | Delay Code 4 Time |
---|---|---|---|---|---|---|---|---|---|---|
1 | AA001 | 09:00 | 93 | 00:10 | 85 | 00:05 | 13 | 00:01 | ||
2 | AA222 | 09:30 | 93 | 00:23 | 5 | 00:23 | ||||
3 | AA049 | 09:45 | 8 | 00:01 | ||||||
4 | AA012 | 10:00 | 93 | 10 | 10 | 00:02 | 19 | 00:05 | 21 | 00:14 |
Seems I attached the wrong file. I have edited my reply above.
The results you are getting is because you are not loading ID in the loop (I think).
Hi, Charlie.
I do not know how well this scales for very big data, but please find the attached document.
I'll Paste the code here as well.
Flight_Records:
load * inline [
ID, Flight Number, STD
1, AA001, 09:00
2, AA222, 09:30
3, AA049, 09:45
4, AA012, 10:00
];
Delay_Codes_t1:
load * inline [
ID, Delay Code, Delay Time
1, 93, 00:10
1, 85, 00:05
1, 13, 00:01
2, 93, 00:23
2, 5, 00:10
3, 8, 00:01
4, 93, 00:10
4, 10, 00:02
4, 19, 00:05
4, 21, 00:14
];
Delay_Codes:
load *, RowNo() as Sort resident Delay_Codes_t1;
tempCount:
load count(DISTINCT [Delay Code]) as NoOfDelayCodes Resident Delay_Codes Group by ID;
maxCount:
load max(NoOfDelayCodes) as maxCount resident tempCount;
let maxCount = peek('maxCount',0,'maxCount');
for x = 1 to $(maxCount)
left join(Flight_Records)
load ID,
FirstSortedValue([Delay Code],Sort,$(x)) as [Delay Code $(x)],
FirstSortedValue([Delay Time],Sort, $(x)) as [Delay Code $(x) Time]
Resident Delay_Codes group by ID;
next x;
drop table Delay_Codes, tempCount, maxCount,Delay_Codes_t1;
Hi,
one solution might be:
LOAD *
FROM [http://community.qlik.com/thread/128536]
(html, codepage is 1252, embedded labels, table is @1);
Generic LOAD
ID,
'Delay Code '&AutoNumber(RecNo(),ID),
[Delay Code]
FROM [http://community.qlik.com/thread/128536]
(html, codepage is 1252, embedded labels, table is @2);
Generic LOAD
ID,
'Delay Time '&AutoNumber(RecNo(),ID),
[Delay Time]
FROM [http://community.qlik.com/thread/128536]
(html, codepage is 1252, embedded labels, table is @2);
but you might consider using pivot tables instead, leaving your data model as is.
hope this helps
regards
Marco
Hi Simen,
thanks for coming back to me, I tried what you said and for some reason I get a bizarre output?
Be great to test this method just need to understand where I am going wrong.
Thanks a lot!
Thanks Martyn,
I will have a read of this today.
Thanks Marco,
I had a play with this method but it seems to hang when I run it on my full dataset. the Delays have 400,000 rows but it got caught at 250k and stopped.
Would this be something I am doing wrong or is this process a biggy
Many Thanks
Seems I attached the wrong file. I have edited my reply above.
The results you are getting is because you are not loading ID in the loop (I think).
Many thanks Simen this is now working great!