Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

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