Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

samir_bihari
New Contributor II

Displaying 2 Hierarchies (each 3 levels in 1 row)

Hi Experts ,

I am new to qlikview and apologies in advance If I am asking something silly .

I am trying to load data from MS analysis services Cube where I have 2 Hierarchies namely Region and Year (each 3 levels) .

I have used the following MDX query after connecting to analysis services :

SELECT

{

  [Measures].[Qty],

  [Measures].[Turnover USD]

}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin(

  {[Area posted].[MG Area posted].Members},

  {[Company].[Company].Children},

    {[Customer].[Customer label].Children},

  {[Product].[Product brand].Children},

  {[Date].[Calendar Y-Q-M-D].Members})

ON ROWS

FROM [Sales]

But it provides the below output :

Eaxmple :

RegionSub RegionAreaYearQuarterMonthCompanyCustomer   LabelSales
AMERN.AmericaAlbania2016TCH111-MANU10
AMERN.AmericaAlbania2016Q1TCH111-MANU10
AMERN.AmericaAlbania2016Q1JanTCH111-MANU10
AMER2016Q1JanTCH111-MANU10
AMERN.America2016Q1JanTCH111-MANU10
AMERN.AmericaAlbania2016Q1JanTCH111-MANU

10

But I want the output as below :

RegionSub RegionAreaYearQuarterMonthCompanyCustomer LabelSales
AMERN.AmericaAlbania2016Q1JanTCH111-MANU

10

Means I don't want any duplicates and all levels in flat 1 row . Attached is the qvw file .

Any pointers how I can achieve that ?

Thanks

Samir