topic Transpose Rows into Columns on Load in QlikView

Transpose Rows into Columns on Load

Thu, 10 Nov 2016 17:58:13 GMT

Hi,

I have been given a qvd file that when loaded looks like this :

What I need to to for every ID is have a single row and the Order Numbers as columns :

ID	1	2	3
1508892	S258	S571	A544

There could be a maximum of 10 Order Numbers.

I need to do this so I can join this onto another QVD in another Dashboard. Is this possible?

Many thanks

Re: Transpose Rows into Columns on Load

Oleg_Troyansky — Thu, 10 Nov 2016 18:33:40 GMT

I'd suggest loading the raw data first, and then looping through the 10 Order Number values (using the function FieldValue() ) and loading the corresponding Codes into the corresponding field names, and concatenating all these loads. Perhaps someone with enough spare time could offer this piece of script...

cheers,

Oleg Troyansky

Learn advanced Qlik techniques in my book QlikView Your Business.

Re: Transpose Rows into Columns on Load

rwunderlich — Thu, 10 Nov 2016 19:03:20 GMT

Add the GENERIC prefix to the load.

Data:

GENERIC

LOAD ID,Order_Number, Code INLINE [

ID, Code, Order_Number

1508892, A544, 3

1508892, S258, 2

1508892, S571, 1

];

-Rob

http://masterssummit.com

http://qlikviewcookbook.com