Data Arrangement

[akpofureenughwu]

Good morning everyone, I need help in arranging my data. If you look at the attached excel sheet, you will noticed that corporate has CAPEX n OPEX under it and different descriptions/item but when I load this file on QV, When I selected corporate, only CAPEX and one item on the description that is highlighted. Can u help with the arrange this data so that when I select corporate, CAPEX n OPEX will be highlighted and even all the descriptions under corporate will be highlighted as well. Thanks

[ramasaisaksoft]

Hi Akpofure,

Just add this script in ur edit script

LOAD Asset,

     Category,

     [Sub-Category],

     Description

FROM

[...\Data Arrangement.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null, case)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

[akpofureenughwu]

Hello Rama, That wouldn't do what I desire..

[sunny_talwar]

This should also work:

LOAD If(Len(Trim(Asset)) = 0, Peek('Asset'), Asset) as Asset,

    If(Len(Trim(Category)) = 0, Peek('Category'), Category) as Category,

    If(Len(Trim([Sub-Category])) = 0, Peek('Sub-Category'), [Sub-Category]) as [Sub-Category],

    Description

FROM

[Data Arrangement.xlsx]

(ooxml, embedded labels, table is Sheet1);

[akpofureenughwu]

THanks Sunny.. Will this code work in Qliksense Environment?..

[oknotsen]

If this load statement works in QlikView, it will also work in Qlik Sense, though you might have to change the source to a library.

[ziadm]

as another option of Sunny script you could user Qlikview file Transformation wizard.  This is an excellent utility to transform or pivot the excel files.  The code generated from qlikview easily can be copied and pasted into qliksense and it 100% compatible

LOAD Asset,

     Category,

     [Sub-Category],

     Description

FROM

[Data Arrangement.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

[akpofureenughwu]

Thanks. How can I do it when I'm using a SQL source?

[sunny_talwar]

May be something like this:

LOAD If(Len(Trim(Asset)) = 0, Peek('Asset'), Asset) as Asset,

    If(Len(Trim(Category)) = 0, Peek('Category'), Category) as Category,

    If(Len(Trim([Sub-Category])) = 0, Peek('Sub-Category'), [Sub-Category]) as [Sub-Category],

    Description;

SQL SELECT *

FROM.....

[akpofureenughwu]

It didn't work.. See Attached pics. Thanks for your assistance so far..