Transformation from the SpreadSheet

[#1]

Dear QV Experts,

Please find enclosed the Input SpreadSheets,

From the file,  need to segregate,

2013-14 YTD

2014-15 YTD

2015-16 YTD &

2013-14 TARGET

2014-15 TARGET

2015-16 TARGET

Customers, Environment/Assets, Employees need to be separated.

Can you please suggest me on this.

Thanks

Sasi

[HirisH_V7 · Accepted solution]

Hi,

Check this way,

Make some transformations too fields as you require,

Data:
CrossTable(Customers, Value, 2)
LOAD F1 as Year, 
     F2 as Month, 
     Line1, 
     Lin2, 
     Line3, 
     Line4, 
     Line5, 
     Line6, 
     Line7, 
     Line8, 
     Line9, 
     Line10, 
     Line11, 
     Line12, 
     [Environment/Assets], 
     Line13, 
     Line14, 
     Line15, 
     Line16, 
     Line17, 
     Line18, 
     Line19, 
     Line20, 
     Line21, 
     Line22, 
     Employees, 
     Line23, 
     Line24, 
     Line25, 
     Line26, 
     Line27, 
     F32
FROM
[Sample.xls]
(biff, embedded labels, header is 1 lines, table is Sheet1$, filters(
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3)),
Remove(Col, Pos(Top, 2)),
Transpose(),
Replace(1, top, StrCnd(null)),
Remove(Row, Pos(Top, 2)),
Remove(Col, Pos(Top, 3))
));

Output:

PFA,

Hope this Helps,

Regards,

Hirish

[#3]

Thanks Hirish. It works for me.

[HirisH_V7]

Its ok.