Column to row conversion.

[lalitkgehlot89]

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.

[PrashantSangle]

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.

like

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,

[lalitkgehlot89]

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.

[PrashantSangle]

AFAIK no.

might be other expert will have any alternate approach.

[thevingo]

Hi Lalit

try Crosstab The Crosstable Load

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