NAVISION - Dimension Set Entry - Pivot Table

[Anonymous]

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

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

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.

[shraddha_g]

Try Creating Hierarchy. Refer hierarchies in load script | Qlik Community