topic Re: Excel Data Transformation in QlikView

Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 09:25:07 GMT

HI Friends,

I have attached the sample data in excel. I would need your help/suggestion on how this data can be loaded into qvw as 2 tables.

Re: Excel Data Transformation

Kushal_Chawda — Tue, 29 Mar 2016 09:34:50 GMT

What is expected output you need?

Re: Excel Data Transformation

tamilarasu — Tue, 29 Mar 2016 09:40:05 GMT

Hi Manoj,

Use below script.

Data:
LOAD Column1, 
     Column2, 
     Column3, 
     Column4
FROM
[Book1 (1).xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
)) where not wildmatch(Column2,'*Column*');

Result:

Re: Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 09:51:26 GMT

Hi Nagaraj, Thanks for your reply.

Data in Row1 is heading for Table1 & Data from row 2 has to go into Table1, same for row6 & row10.

Data in Row3 will become heading for table2 & row5,row6 are values for table2

Re: Excel Data Transformation

tamilarasu — Tue, 29 Mar 2016 09:53:31 GMT

Could you post your expected output (How you want to see) in same excel.?

Re: Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 09:55:10 GMT

Table1:

Column1	Column2	Column3	Column4
Test	2000018	8/03/2016 5:22:03 PM	9/03/2016 3:22:03 AM
Test2	2000018	8/03/2016 5:22:03 PM	9/03/2016 3:22:03 AM
Test4	2000018	8/03/2016 5:22:03 PM	9/03/2016 3:22:03 AM

Table2

Column1	Column2	Column3
100	2780970	8/03/2016 5:22:03 PM
100	2780671	8/03/2016 5:21:15 PM
200	2780970	8/03/2016 5:22:03 PM
200	2780671	8/03/2016 5:21:15 PM
300	2780970	8/03/2016 5:22:03 PM
300	2780671	8/03/2016 5:21:15 PM

Re: Excel Data Transformation

avinashelite — Tue, 29 Mar 2016 10:00:37 GMT

Please post the desired output format with your data set ..

Re: Excel Data Transformation

tamilarasu — Tue, 29 Mar 2016 10:01:42 GMT

You can try below script.

QUALIFY *;
Table1:
LOAD Column1, 
     Column2, 
     Column3, 
     Column4
FROM
[Book1 (1).xlsx]
(ooxml, embedded labels, table is Sheet1) where Len(Trim(Column1))>0;

Table2:
LOAD Column2 as Column1, 
     Column3 as Column2, 
     Column4 as Column3
FROM
[Book1 (1).xlsx]
(ooxml, embedded labels, table is Sheet1) where Len(Trim(Column1))=0 and not wildmatch(Column2,'*Column*');

Re: Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 10:20:38 GMT

Hi Nagraj, Thanks for your quick reply. It worked, but i did mistake in putting column names. Please find attached latest data

Re: Excel Data Transformation

tamilarasu — Tue, 29 Mar 2016 10:26:20 GMT

Fine Manoj. Try the below script

Table1:
LOAD Column1,
     Column2,
     Column3,
     Column4
FROM
[Book1 (1)(1).xlsx]
(ooxml, embedded labels, table is Sheet1) where Len(Trim(Column1))>0;


Table2:
LOAD  Column2 as Column11,
      Column3 as Column12,
     Column4 as Column13
FROM
[Book1 (1)(1).xlsx]
(ooxml, embedded labels, table is Sheet1) where Len(Trim(Column1))=0 and not wildmatch(Column2,'Column*');

Re: Excel Data Transformation

avinashelite — Tue, 29 Mar 2016 10:28:06 GMT

If you have got the answer mark the helpful and correct answers

Re: Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 10:29:56 GMT

I didn't understand the statement not wildmatch(Column2,'*Column*') ? Why we would need this ?

Re: Excel Data Transformation

avinashelite — Tue, 29 Mar 2016 10:36:12 GMT

wildmatch(Column2,'*Column*')

wildmatch is case insensitive , it will try to compare the value of the parameter 1 with 2nd if the match is found it will return true ..using this we can validate the conditions according to our need

Re: Excel Data Transformation

tamilarasu — Tue, 29 Mar 2016 10:48:41 GMT

We need to exclude the header names (Column11, Column 12 and Column 13) in table 2. So the above syntax checks the column1 whether any cells contains "Column*" and excluds that specfic row.

Re: Excel Data Transformation

manojkulkarni — Tue, 29 Mar 2016 11:19:48 GMT

Thanks for your suggestion.