Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
bwisenosimenkg
Valued Contributor

Re: Trying to convert multiple rows into one row and multiple columns

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

8 Replies
bwisenosimenkg
Valued Contributor

Re: Trying to convert multiple rows into one row and multiple columns

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
Contributor III

Re: Trying to convert multiple rows into one row and multiple columns

Hi,

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

Marty.

Re: Trying to convert multiple rows into one row and multiple columns

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

Re: Trying to convert multiple rows into one row and multiple columns

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

Re: Trying to convert multiple rows into one row and multiple columns

Thanks Martyn,

I will have a read of this today.

Not applicable

Re: Trying to convert multiple rows into one row and multiple columns

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

bwisenosimenkg
Valued Contributor

Re: Trying to convert multiple rows into one row and multiple columns

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

Re: Trying to convert multiple rows into one row and multiple columns

Many thanks Simen this is now working great!