topic Re: Expression not recognising dimension despite P() in QlikView

Expression not recognising dimension despite P()

Wed, 13 May 2015 03:08:37 GMT

can you please look at the 2nd expression. I expect it to respect the dimension in iteration in terms of the P() function. But it doesn't. Can someone help?

Thanks,

Ram

Re: Expression not recognising dimension despite P()

danieloberbilli — Wed, 13 May 2015 03:20:58 GMT

the dimension is from tablea

the first expression sums the values from tablea > everything fine

but the second expression sums the values from tableb...so no matter what you write in set analysis, if your dimension is from the (unlinked) tablea there wont be any differences per dimension (of tablea).

one possible solution for expression 2 would be:

sum(if(segment_code=t_segment_code, t_actual_amt))

Re: Expression not recognising dimension despite P()

danieloberbilli — Wed, 13 May 2015 03:25:47 GMT

the problem is that the p() function does not tell qlik how to allocate the sum(value) across the unlinked dimension

Re: Expression not recognising dimension despite P()

Wed, 13 May 2015 04:30:07 GMT

Thanks Daniel for your prompt response.

the sum(if should work. Put in the expression and the dashboard hangs for 30 mins and didn't come back. I think if is very slow performing compared to set analysis in this case. I agree with you on what you say about P() in terms of unlinked dimension. Can there be any other workaround in the context of set analysis?

Re: Expression not recognising dimension despite P()

danieloberbilli — Wed, 13 May 2015 05:08:31 GMT

what about if both tables share segment as a common dimension? Scripting the segment in a separate dimension table and let both tables be linked to that dimension. This would also enable your set analysis expression you had before.

Re: Expression not recognising dimension despite P()

Wed, 13 May 2015 05:15:21 GMT

I tried, doesn't work. The data model is too complicated and this chart is just one of the 100s of charts and all other work fine because i don't have to show the data from both these tables in the same chart. this is the only chart where i have the requirement. Can't afford to touch the data model just for this one chart and introduce a lot of regression.

Re: Expression not recognising dimension despite P()

danieloberbilli — Wed, 13 May 2015 05:26:09 GMT

Maybe try this in your dimension (instead of the grouped dimension)

=aggr(only(segment_code),t_segment_code)

Re: Expression not recognising dimension despite P()

danieloberbilli — Wed, 13 May 2015 05:28:29 GMT

one more idea: try to force the user to limit/reduce the data before showing/calculating the table if this is possible...e.g. let the user set a certain start/end date or selection on certain segments/categories whatever works out

Re: Expression not recognising dimension despite P()

Wed, 13 May 2015 05:30:39 GMT

Hi Daniel, Gives me a NULL in the dimension.

Re: Expression not recognising dimension despite P()

Wed, 13 May 2015 05:34:28 GMT

actually i made a very simple chart with sum(if expression in my data model. simply hangs..... but as the test harness suggests it works but just the sheer volume i believe brings it down.

Re: Expression not recognising dimension despite P()

jonathandienst — Wed, 13 May 2015 05:56:14 GMT

It is slow performing because there is no association between the 2 tables, so QV has to do a cross-join (aka Cartesian join) to perform this filtering. This kind of operation is only practical for small data sets.

As Daniel suggested, the solution is to associate the tables by loading t_segment_code as segment code. If you cant make that change, then you will not be able to do the chart in your post.

Re: Expression not recognising dimension despite P()

Wed, 13 May 2015 06:27:30 GMT

YES Jonathan, I realise it doesn't scale for high data volumes at all as if statements are single threaded. I am trying to see what i can do with the data model with least possible regression effects.