Qlikview Data Representation

[khigaurav999]

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

[karthikoffi27se]

Hi Gaurav,

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

Many Thanks

Karthik

[khigaurav999]

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.

[Peter_Cammaert]

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.

[Or]

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

[dinuwanbr]

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

[khigaurav999]

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

[Peter_Cammaert]

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