Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Trying to convert multiple rows into one row and multiple columns

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

IDFlight NumberSTD
1AA00109:00
2AA22209:30
3AA04909:45
4AA012

10:00

#2 Delay Table

IDDelay CodeDelay Time
19300:10
18500:05
11300:01
29300:23
2500:10
3800:01
49300:10
41000:02
41900:05
42100:14

And this is what we would like to get as an output ideally from the load script:

IDFlight NumberSTDDelay Code 1Delay Code 1 TimeDelay Code 2Delay Code 2 TimeDelay Code 3Delay Code 3 TimeDelay Code 4Delay Code 4 Time
1AA00109:009300:108500:051300:01
2AA22209:309300:23500:23
3AA04909:45800:01
4AA01210:0093101000:021900:052100:14
1 Solution

Accepted Solutions
simenkg
Specialist
Specialist

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).

View solution in original post

8 Replies
simenkg
Specialist
Specialist

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;

martynlloyd
Partner - Creator III
Partner - Creator III

Hi,

Check out 'The Generic Load' in byHenric Cronström in Qlik Community.

Marty.

MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_128536_Pic1.JPG.jpg

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

Not applicable
Author

Hi Simen,

thanks for coming back to me, I tried what you said and for some reason I get a bizarre output?

Untitled.png

Be great to test this method just need to understand where I am going wrong.

Thanks a lot!

Not applicable
Author

Thanks Martyn,

I will have a read of this today.

Not applicable
Author

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

simenkg
Specialist
Specialist

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).

Not applicable
Author

Many thanks Simen this is now working great!