Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paragp12
New Contributor

NAVISION - Dimension Set Entry - Pivot Table

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.

1 Reply
shraddha_g
Honored Contributor III

Re: NAVISION - Dimension Set Entry - Pivot Table

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

Community Browser