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

Trying to convert multiple rows into one row and multiple columns

Wed, 06 Aug 2014 16:21:37 GMT

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

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

simenkg — Wed, 06 Aug 2014 16:51:17 GMT

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;

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

martynlloyd — Wed, 06 Aug 2014 17:06:28 GMT

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

MarcoWedel — Wed, 06 Aug 2014 17:29:01 GMT

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

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

Fri, 08 Aug 2014 08:30:53 GMT

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!

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

Fri, 08 Aug 2014 08:32:31 GMT

Thanks Martyn,

I will have a read of this today.

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

Fri, 08 Aug 2014 08:34:08 GMT

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

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

simenkg — Fri, 08 Aug 2014 09:15:52 GMT

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

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

Fri, 08 Aug 2014 13:10:30 GMT

Many thanks Simen this is now working great!