topic Re: Categorisation in drill down dimension in QlikView

Categorisation in drill down dimension

Wed, 10 May 2017 01:46:46 GMT

Hello
I have created a drill down like below

Office:
LOAD * INLINE [

Supplies , SupplyInfo
Board, WhiteBoard
Board, BlackBoard
Board, WhiteBoards
Board, BlackBoards
];

I have some other defined as well and I am partially able to achieve the purpose where on a bar chart it shows me how many items in each category and when I click on any category it drill down to that category and re draws the bar chart and shows how many white board and how many black board.

But the problem is that data is not clean and it includes all different versions and spelling variations like WhiteBoards,whiteboard etc.

So in the above example when I drill down into boards I see 4 bars for each item in the bar chart but essentially I only want two bars, where each one combines the count of all kinds of whiteboards or blackboards
How do I define that "WhiteBoards","whiteboard,", "mobilewhiteboard" are all same category. Something like this

Office:
LOAD * INLINE [

Supplies , SupplyInfo
Board, *WhiteBoard*
Board, *BlackBoard*
];

Thanks

Re: Categorisation in drill down dimension

RonaldDoes — Wed, 10 May 2017 07:32:12 GMT

Hi Cimon,

You will want to add a "category" to each SupplyInfo with all supplies defined. That way you can use the category "whiteboard" as a dimension in your chart.

Office:

LOAD * INLINE [

Supplies , SupplyInfo

Board, WhiteBoard

Board, BlackBoard

Board, WhiteBoards

Board, BlackBoards

Board, whiteboard

Board, mobilewhiteboard

];

Office_categories:

LOAD * INLINE [

SupplyCategory , SupplyInfo

Whiteboard, WhiteBoard

Blackboard, BlackBoard

Whiteboard, WhiteBoards

Blackboard, BlackBoards

Whiteboard, whiteboard

Whiteboard, mobilewhiteboard

];

I've attached an example. Hope this helps you.

With kind regards,

Ronald

Re: Categorisation in drill down dimension

ahaahaaha — Wed, 10 May 2017 08:22:38 GMT

Hi,

May be like this (look attached file)

//create map table

MapTable:

Mapping LOAD*Inline

[A , B

WhiteBoard, WhiteBoard

WhiteBoards, WhiteBoard

BlackBoard, BlackBoard

BlackBoards, BlackBoard

whiteboards, WhiteBoard

blackBoard, BlackBoard

];

//create source data

OfficeTemp:

LOAD * INLINE [

Supplies , SupplyInfo

Board, WhiteBoard

Board, BlackBoard

Board, WhiteBoards

Board, BlackBoards

Board, whiteboards

Board, blackBoard

];

//create category

NoConcatenate

Office:

LOAD Distinct Supplies,

MapSubstring('MapTable', SupplyInfo) as SupplyInfo

Resident OfficeTemp;

Drop Table OfficeTemp;

Result

Regards,

Andrey