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

Pivot Table not Grouping data to correct parent Qlik Sense

Sat, 07 Mar 2015 08:05:33 GMT

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.

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

Sat, 07 Mar 2015 10:32:58 GMT

Hi,

Please try this in your script:

___

Test:

Mapping

LOAD

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

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

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

Sat, 07 Mar 2015 12:36:32 GMT

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