Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Pivot Table not Grouping data to correct parent Qlik Sense

Dear Team,


I have data where ledgers have balances with and without department.

I have tried to group totals of ledger balances via uploading excel data as shown below. As per logic i have to take department also in my excel sheet..


Now i need to see total of ledgers against the PD field in excel that is linked to real data via ledger and department field. (Sale, Direct Mat, Indirect Mat, Labor & Ovhd etc).


In Pivot table i cant see grouped total where PD = Sale, Direct Material, Indirect Material. Dept field is blank in this data.


The grouped total is correct for Labor and Ovhd. where department field is available in the data.

I added relevant document and sample data.

I

2 Replies
Not applicable

Re: Pivot Table not Grouping data to correct parent Qlik Sense

Hi,

Please try this in your script:

___

Test:

Mapping

LOAD

  [Dept] &'|'&[Ledger],

  PD

FROM [lib://Ledgerb/PL.xlsx]

(ooxml, embedded labels, table is Sheet1);

LOAD

    "Year",

    Period,

    [Ledger],

    [Dept],

    Ballance,

    ApplyMap('Test',[Dept] &'|'&[Ledger],'Not available') as PD

FROM [lib://Ledgerb/Ballance.xlsx]

(ooxml, embedded labels, table is Sheet1);

//the following can also be an apply map but it wasn't the cause of synthetic keys

LOAD

    Ledger,

    "L Desc"

FROM [lib://Ledgerb/Ballance.xlsx]

(ooxml, embedded labels, table is Sheet2);

____

Hope this helps.

Kind regards,

Nuno

Not applicable

Re: Pivot Table not Grouping data to correct parent Qlik Sense

Thanks a Lot Nuno..

This has added rest of the data (Ballance) to not available index.

Instead i need to total it up against sales, Direct Labor etc..

I tried maintaining dummy department in transaction as well as in the PD master, this has brought the result.

But actually transactions would come from ODBC so is there any solution to sync blank department too.

thanks

Shiv

Community Browser