2 Replies Latest reply: Mar 7, 2015 7:36 AM by Shiv Sharma RSS

    Pivot Table not Grouping data to correct parent Qlik Sense

    Shiv Sharma

      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

       

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

          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