Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transformation from the SpreadSheet

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

1 Solution

Accepted Solutions
HirisH_V7
Master
Master

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:

Excel Load-205950.PNG

PFA,

Hope this Helps,

Regards,

Hirish

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

3 Replies
HirisH_V7
Master
Master

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:

Excel Load-205950.PNG

PFA,

Hope this Helps,

Regards,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
Not applicable
Author

Thanks Hirish. It works for me.

HirisH_V7
Master
Master

Its ok.

HirisH
“Aspire to Inspire before we Expire!”