topic Re: Qlikview Data Representation in QlikView

Qlikview Data Representation

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

Hi Folks,

I am new to QlikView and facing an issue in representing data in the expected way.

I have below data source that looks like this i.e. highlighted fields I am capturing in data load and representing:

My challenge here is to present it in four records rather 8 records i.e. if you see "High Level Item" field is comman / link b/w WBS Element and Network Number here. I want to present it as - PO Numer, WBS Element, NetWork Number in four records for this example.

One way I can think of is doing some transformation at Load Scrip level i.e. removing duplicates in High Level Item field and using it a join field b/w other 2 but want to see if there are other better approaches and / or something can be done at object level within this table representation.

Thanks,

Gaurav

Re: Qlikview Data Representation

karthikoffi27se — Mon, 11 Sep 2017 12:53:29 GMT

Hi Gaurav,

Try using applymap, finding duplicates with yes and no and use it in the table.

Many Thanks

Karthik

Re: Qlikview Data Representation

khigaurav999 — Mon, 18 Sep 2017 09:22:47 GMT

Is there any other way to it rather than apply map? Duplicate is not the problem, high level field is the link b/w other two.., we need to represent it in a meaningful way i mean i want represent it in 4 records rather than 8.

Re: Qlikview Data Representation

Peter_Cammaert — Mon, 18 Sep 2017 09:38:39 GMT

There is a simple possible solution, but it only works under strict conditions (no multiple values in the fields-to-be-merged, just a NULL and a single value).

Do not use a table box, but create a straight table and add all common fields as dimensions (e.g. [PO / CR Number], [High Level Item] etc.). Then add the partially filled fields as expressions like for exmaple:

=MaxString([WBS Element])

=MaxString([Network Number])

etc.

But the better solution would be to split the data into different internal tables and let the common fields act as key. Then QlikView will do the heavy lifting.

Re: Qlikview Data Representation

Or — Mon, 18 Sep 2017 10:08:03 GMT

This sounds like a job for GROUP BY.

Load [PO Number],

[High Level Item],

max(WBS_Element) as WBS_Element,

max(Network_Number) as Network_Number

From YourTable

Group By [PO Number],

[High Level Item];

Re: Qlikview Data Representation

dinuwanbr — Mon, 18 Sep 2017 11:09:14 GMT

Hi Gaurav,

Try this

on object level

Create a chart and give one Filed (suppose say PO/CR number) as one dimension and other fields as expressions (without any formula).

on script level

you can use join (left join by uploading as two tables) or applymap()

or a group by clause with max()

Rgds,

Tharindu

Re: Qlikview Data Representation

khigaurav999 — Mon, 18 Sep 2017 12:16:09 GMT

Thanks. I am receiving following error.. what is wrong?

Invalid expression

ZASR32:

LOAD [Sales Doc Type],

[Sales Document] as Document,

Item,

[High Level Item],

Material,

[Material Description],

max([WBS Element]) as [WBS Element],

[PO Description] as [PO Description ZASR32],

max([Network Number]) as [Network Number]

FROM

[File Location]

(ooxml, embedded labels, table is ZASR32_RPT_062117)

GROUP BY [Sales Document],[High Level Item];

Re: Qlikview Data Representation

Peter_Cammaert — Mon, 18 Sep 2017 15:23:47 GMT

In a LOAD statement that has a GROUP BY clause, all fields that are not listed in the GROUP BY clause should be used as parameter in a call of an aggregation function like max(), sum() or count(). Or Shohams code complies with that rule. But you added a lot of fields that aren't present in an aggregation function call and are not present in the GROUP BY clause.

You can use

LOAD Only([Sales Doc Type]) AS [Sales Doc Type],

[Sales Document] as Document,

Only(Item) AS Item,

[High Level Item],

Only(Material) AS Material,

Only([Material Description]) AS [Material Description],

max([WBS Element]) as [WBS Element],

Only([PO Description]) as [PO Description ZASR32],

max([Network Number]) as [Network Number]

FROM [File Location](ooxml, embedded labels, table is ZASR32_RPT_062117)

GROUP BY [Sales Document],[High Level Item];

But only if for these new fields there is only one value for every combination of the GROUP BY field values.

Best,

Peter