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 :
Region | Sub Region | Area | Year | Quarter | Month | Company | Customer Label | Sales |
AMER | N.America | Albania | 2016 | | | TCH | 111-MANU | 10 |
AMER | N.America | Albania | 2016 | Q1 | | TCH | 111-MANU | 10 |
AMER | N.America | Albania | 2016 | Q1 | Jan | TCH | 111-MANU | 10 |
AMER | | | 2016 | Q1 | Jan | TCH | 111-MANU | 10 |
AMER | N.America | | 2016 | Q1 | Jan | TCH | 111-MANU | 10 |
AMER | N.America | Albania | 2016 | Q1 | Jan | TCH | 111-MANU | 10 |
But I want the output as below :
Region | Sub Region | Area | Year | Quarter | Month | Company | Customer Label | Sales |
AMER | N.America | Albania | 2016 | Q1 | Jan | TCH | 111-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