topic Re: Data Arrangement in QlikView

Data Arrangement

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Data Arrangement

ramasaisaksoft — Tue, 31 May 2016 13:20:44 GMT

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))

));

Re: Data Arrangement

akpofureenughwu — Tue, 31 May 2016 13:32:43 GMT

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

Re: Data Arrangement

sunny_talwar — Tue, 31 May 2016 13:49:40 GMT

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

Re: Data Arrangement

akpofureenughwu — Tue, 31 May 2016 14:00:36 GMT

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

Re: Data Arrangement

oknotsen — Tue, 31 May 2016 15:45:52 GMT

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.

Re: Data Arrangement

ziadm — Tue, 31 May 2016 16:28:44 GMT

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))

));

Re: Data Arrangement

akpofureenughwu — Fri, 03 Jun 2016 09:05:53 GMT

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

Re: Data Arrangement

sunny_talwar — Fri, 03 Jun 2016 12:02:22 GMT

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.....

Re: Data Arrangement

akpofureenughwu — Fri, 03 Jun 2016 14:24:18 GMT

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

Re: Data Arrangement

sunny_talwar — Fri, 03 Jun 2016 14:26:05 GMT

Order is something like this:

1) OLEDB....

2) LOAD...

3) SQL SELECT ....

Re: Data Arrangement

akpofureenughwu — Fri, 03 Jun 2016 14:40:27 GMT

Not working yet. See attachements

Re: Data Arrangement

sunny_talwar — Fri, 03 Jun 2016 14:44:07 GMT

Field names to match exactly, I think the field might not be called Group and Description, is it?

Re: Data Arrangement

akpofureenughwu — Fri, 03 Jun 2016 15:04:57 GMT

Hey man, They are.. I used the exact field name. See attachement. The original excel sheet, I loaded it into the sql database environment.

Re: Data Arrangement

sunny_talwar — Fri, 03 Jun 2016 15:48:18 GMT

Not sure what might be causing this. May be the data isn't correctly sorted for it to work as we would expect.

Re: Data Arrangement

akpofureenughwu — Fri, 03 Jun 2016 15:59:29 GMT

So what is the next course of action sir?

Re: Data Arrangement

sunny_talwar — Fri, 03 Jun 2016 16:01:29 GMT

Would you be able to share the application after running just the query (without the preceding load)