Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The attached excel has two sheets one is input and other has output.
I need output as shown in output sheet please help..
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);
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...
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
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..
Please open new thread if your initial question is answered.
Thanks
Regards
Marco