Data fetching from excel using cross table.

[agni_gold]

The attached excel has two sheets one is input and other has output.

I need output as shown in output sheet please help..

[#2]

Hi

Not sure how many columns you are looking at but this will work for the excel you attached.

Test:

LOAD ID,

     [Week end],

     Date,

      Sales,

     Cost,

     Profit

FROM

(ooxml, embedded labels, table is INPUT);

Concatenate

LOAD ID,

     [Week end1] as [Week end],

     Date1 as Date,

     Sales1 as Sales,

     Cost1 as Cost,

     Profit1 as Profit

FROM

(ooxml, embedded labels, table is INPUT);

Concatenate

LOAD ID,

     [Week end2]as [Week end],

     Date2 as Date,

     Sales2 as Sales,

     Cost2 as Cost,

     Profit2 as Profit

FROM

(ooxml, embedded labels, table is INPUT);

[agni_gold]

Thanks for reply

But this is just a test data but i have a excel that have approx 53 times same columns . i thinks to write this script 53 times is not generic .

I just want a generic logic so that i can automate this process also...

[MarcoWedel]

Hi,

one possible solution:

table1:
CrossTable (FieldName, FieldValue)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/604414-123983/test%20data.xlsx] (ooxml, embedded labels, table is INPUT);

Left Join (table1)
LOAD Distinct
  FieldName,
  PurgeChar(FieldName, '0123456789') as FieldName2,
  KeepChar(FieldName, '0123456789') as Rec
Resident table1;

table2:
Generic LOAD
  ID,
  Rec,
  FieldName2,
  FieldValue
Resident table1;

DROP Table table1;

hope this helps

regards

Marco

[agni_gold]

Thanks very helpful

I have one more scenario  . I am attaching the updated test excel

Please look over the output and input sheets .

please help..

[MarcoWedel]

Please open new thread if your initial question is answered.

Thanks

Regards

Marco