topic Re: Column to row conversion. in QlikView

Column to row conversion.

lalitkgehlot89 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

In Sample data I want KON,DAL,DDS in a new column name Store_Type and W/S-TOTAL, Retail-PSC, RETAIL-PCC,RETAIL-TOTAL in a new column name SUBStore_Type and value should be in new column QTY.

Please help me out to convert it.

Re: Column to row conversion.

PrashantSangle — Thu, 29 Mar 2018 06:45:23 GMT

Hi Lalit,

there is no direct or easy way to achieve your requirement. You have to load same table multiple time and concatenate it. Every time you have to load with required field and create required new column.

Test:

LOAD F1,

F2,

F3,

F4,

F5,

F6,

F7,

F8,

F9,

F10,

F11,

TOTAL as Qty,

'W/S-Total' as SUBStore_Type,

'KON' as Store_Type

FROM

[..\..\Prashant Profile\Desktop\To Be Deleted\sample data (1).xlsx]

(ooxml, embedded labels, header is 3 lines, table is Sheet1);

join //or concatenate //use as per your data granularity

LOAD F1,

F2,

F3,

F4,

F5,

F6,

F7,

F8,

F9,

F10,

F11,

PSC as Qty,

'Retail - PSC' as SUBStore_Type,

'KON' as Store_Type

FROM

[..\..\Prashant Profile\Desktop\To Be Deleted\sample data (1).xlsx]

(ooxml, embedded labels, header is 3 lines, table is Sheet1);

Regards,

Re: Column to row conversion.

lalitkgehlot89 — Thu, 29 Mar 2018 07:46:30 GMT

Hi Prasant,

In Case of 50+ Store_Type and 4-5 SUBStore_Type that will be very difficult and time consuming also.

don't we have any alternate option.

Re: Column to row conversion.

PrashantSangle — Thu, 29 Mar 2018 09:49:11 GMT

AFAIK no.

might be other expert will have any alternate approach.

Re: Column to row conversion.

thevingo — Thu, 29 Mar 2018 09:54:49 GMT

Hi Lalit

try Crosstab The Crosstable Load

Also, if your columns/No. of stores are not fixed, you can try loop.