Excel Data Transformation

[manojkulkarni]

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.

[Kushal_Chawda]

What is expected output you need?

[tamilarasu]

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:

[manojkulkarni]

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

[tamilarasu]

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

[manojkulkarni]

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

[avinashelite]

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

[tamilarasu]

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

[manojkulkarni]

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

[tamilarasu]

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