1 Reply Latest reply: Nov 23, 2017 11:36 PM by Shraddha Gajare RSS

    NAVISION - Dimension Set Entry - Pivot Table

    Parag Parashar

      We implemented QLIK Sense dashboards with NAVISION as source. In Navision there is a table "Dimension Set Entry" table which has additional information about the Transaction Lines. Additional information such as the BRANCH, DIVISIONS, SECTOR and any such customized information with respect to the Transaction Lines can be added.

      The data exists in the table in the following format using 4 columns.

      Table: Dimension Set Entry

      DSI.jpg

      We were required to add filters on the Dashboard by BRANCH, DIVISIONS, DEPARTMENTS, SECTOR and other such Dimension Codes. The "Dimension Set Id" was linked to the Transaction Lines.

      To achieve the above associations we required the data to be in the following format.

       

       

      PIVOT Table

      DSI_Pivot.jpg

       

       

      We achieved the above using the following script in QLIK Sense (script within *****).

      I have just given an example using 4 Dimension Codes. There could be many more.

       

       

      ***********************************************************************************************************

      LIB CONNECT TO 'Microsoft_SQL_Server_XXXX (XXXXX)';

       

       

      TEMP_DSE_Pivot:

       

       

      select

      "Dimension Set ID","BRANCH","SECTOR","DIVISIONS","DEPARTMENT"

      from (

      select "Dimension Set ID", "Dimension Code", "Dimension Value Code" from

              "ABC".dbo."XYZ$Dimension Set Entry"

      ) dse

      pivot

      (

      max("Dimension Value Code") for "Dimension Code" in

              ("BRANCH","SECTOR","DIVISIONS","DEPARTMENT")  

      )

      as pvt;

       

       

      Store TEMP_DSE_Pivot into [Lib://GenericQVD (xxxxx)/TEMP_DSE_Pivot.QVD] (qvd);

       

       

      ***********************************************************************************************************

       

       

      I hope this has been useful.