topic Re: Re: Data fetching from excel using cross table. in QlikView

Data fetching from excel using cross table.

agni_gold — Mon, 08 Sep 2014 17:50:46 GMT

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

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

Re: Data fetching from excel using cross table.

Mon, 08 Sep 2014 18:19:12 GMT

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

Re: Data fetching from excel using cross table.

agni_gold — Mon, 08 Sep 2014 18:23:46 GMT

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

Re: Data fetching from excel using cross table.

MarcoWedel — Mon, 08 Sep 2014 19:06:56 GMT

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

Re: Re: Data fetching from excel using cross table.

agni_gold — Mon, 08 Sep 2014 20:20:33 GMT

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

Re: Data fetching from excel using cross table.

MarcoWedel — Mon, 08 Sep 2014 20:48:22 GMT

Please open new thread if your initial question is answered.

Thanks

Regards

Marco