topic Re: Tricky questions about set analysis in QlikView

Tricky questions about set analysis

Thu, 05 Jul 2012 19:24:45 GMT

Hi,

I have loaded two tables, Table A & Table B. (Table A & B are not directly linked)

In table A, there are columns A1, A2, A_Year

In table B, there are columns B1, B2, B3, B_Year

And then I have one sheet, where I have added a multi box, with fields A1 & A2.

Then I created a bar chart, using A_Year as dimension, and in the expression field, I used set analysis to check whether B1=P(A1) and B2=P(A2)

The syntax is like this: count({<B1= P([A1]), B2= P([A2])>} B3)

The tricky part for me is that B1 has more values than A1, and B2 has more values than A2. So with the above expression, the overall count of B table is not correct. Is there anyway to capture all the rows in B, instead of dropping the unique values in table B?

Thanks!

Re: Tricky questions about set analysis

Thu, 05 Jul 2012 19:57:41 GMT

the parameter P() demands a selection. If you choose some values on field A1, it will work.

If you limit the field A1 with set analysis, it will work too.

Re: Tricky questions about set analysis

Thu, 05 Jul 2012 20:01:10 GMT

Hi, thanks for the comments. But my situation is that I have to capture all the values in table B, by default.

The set analysis actually will drop the unique ones in table B.

Re: Tricky questions about set analysis

Thu, 05 Jul 2012 20:16:58 GMT

I have made some more tests and it worked fine, with no selection. Sorry, my mistake.

I could not figure why you want to count a B column having a dimension A. It would be a cartesian product in SQL.

shouldn´t the dimension be B_year?

Qlikview apart, what do you want to accomplish with the BI?

Re: Tricky questions about set analysis

Thu, 05 Jul 2012 20:32:45 GMT

I want to calculate %, using count(A)/Count(B). But the issue is that I have to calculate the overall percentage even the values are only unique in table B.

I don't want to do this way in SQL or using outer join in QlikView, because the performance issue (I think those methods will slow down my program).

Re: Tricky questions about set analysis

Thu, 05 Jul 2012 22:21:50 GMT

Is this feasible? Please help.