Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks Hirish. It works for me.
Its ok.