topic Drill Down to multiple fields in QlikView

Drill Down to multiple fields

praveenak — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a situation here.

I have three fields coming from two different tables.My situation is like when I drill down a field in Straight table how can I display two fields in the next hierarchy level of the group.

For example:

A Table shows like below,

Name	Region	Sum(sales)
xxx	ppp	###
yyy	qqq	###
zzz	rrr	###

When selected/drill down on region, it should look like below,

Case-1: provided the State and City are coming from same as table as that of Region

Case-2: provided the State and City are coming from different table as that of Region

Name	State	City	Sum(sales)
xxx	ttt	mmm	###
yyy	sss	nnn	###
zzz	ggg	ooo	###

Thanks in Advance

Re: Drill Down to multiple fields

MK9885 — Tue, 01 Nov 2016 17:44:20 GMT

I'm not sure I correctly understand you...

Help me understand more please?

You want

Name

State

City

Sum(Sales)

In the above way? If that's the case then add dimensions in same way

Name, State, City and then expression as Sum(Sales). You'll get that view.

I don't think it will matter if the fields are coming from different table. As long as there is a link between these fields, the data will populate and you will be able to make selection.

Re: Drill Down to multiple fields

praveenak — Wed, 02 Nov 2016 13:40:06 GMT

Hi,

I want like below,

Name ------------------------------------level-2 hierarchy

State,City -------------------------level-1 hierarchy (both state and city are at same level)

Sum(sales) -----------------level-0 hierarchy

and this to be happened when I selected a Region in table like below

Name ------------------------------------level-2 hierarchy

Region -----------------------------level-1 hierarchy

Sum(sales) ------------------level-0 hierarchy

i.e., The state and city are to be at same hierarchy level instead of city being the next leaf level for State

Hope this gives clear picture.

Re: Drill Down to multiple fields

MK9885 — Wed, 02 Nov 2016 14:43:50 GMT

State&' '&City as StateCity

You can use the above syntax to join 2 dimensions into one. Use that in script editor.

and in front end use

Name as first dimension

StateCity as 2nd Dimension

Sum(Sales) as expression

For 2nd table

Name as first dimension

Region as 2nd dimension

Sum(Sales) as expression

Re: Drill Down to multiple fields

praveenak — Wed, 02 Nov 2016 15:53:52 GMT

Thanks Arvind for the solution. But am looking for the State and City to be different fields rather making them as single field.

Re: Drill Down to multiple fields

MK9885 — Wed, 02 Nov 2016 17:02:28 GMT

You'll still have State and City as different fields and then you'll have a new field StateCity.

You can't have 2 fields as one in a Pivot Table. They are separate dimensions, you can use above syntax I mentioned and create a new field instead and only use that field in Pivot Table.