topic Re: Concatenated Fact Table Issue in QlikView

Concatenated Fact Table Issue

Tue, 21 Apr 2015 19:09:48 GMT

Hello. I am having trouble calculating the % part churn by a dimension. I have an example data set included.

The part churn is calculated by =Count(Part)/Sum(Total_Part_Count). I am able to graph by manager using =Count(Part)/Aggr(Total_Part_Count, Key) but as soon as I select a Manager the denominator is obviously null.

Is there a way that when I filter to a manager I can still get the Total_Part_Count for the key? I have tried several ways with Set Analysis but nothing has worked. Any help would be great. Thank you.

Part	Manager	Key	Total_Part_Count
Part 1	Joe	1A
Part 2	Joe	1A
Part 3	Bill	2B
Part 4	Bill	2B
Part 5	Ted	3C
		1A	5
		2B	10
		3C	1

Re: Concatenated Fact Table Issue

Anonymous — Tue, 21 Apr 2015 20:45:22 GMT

you may use set Analysis to desekect Manager

like this

Count({<Manager=>}Part)

the part {<Manager=>} must be placed in every calculation - Count, sum

Re: Concatenated Fact Table Issue

sunny_talwar — Tue, 21 Apr 2015 20:48:26 GMT

Are you sure you want to do a concatenate here? To me you will be better off doing a Left Join (or Join)

Table:

LOAD Part,

Manager,

Key

FROM xyz;

Join(Table)

LOAD Key,

Total_Part_Count

FROM abc;

Best,

Sunny

Re: Concatenated Fact Table Issue

Wed, 22 Apr 2015 15:29:04 GMT

I tried both suggestions to either join the data or use set analysis but could not get either to work. Any time I filter to a manager I lose the total_parts in the denominator. Any other suggestions?

I have included the qvw for assistance.

Re: Concatenated Fact Table Issue

sunny_talwar — Wed, 22 Apr 2015 15:37:34 GMT

Have a look with join. (PFA)

Best,

Sunny

Re: Concatenated Fact Table Issue

Wed, 22 Apr 2015 15:51:35 GMT

It looks like it is working. The only difference that I had done was an inner join. Thank you!

Re: Concatenated Fact Table Issue

sunny_talwar — Wed, 22 Apr 2015 15:52:48 GMT

Inner join should have also worked, not sure why that did not work for you.

Best,

Sunny