topic Re: Data Island and Set Analysis in QlikView

Data Island and Set Analysis

sunny_talwar — Thu, 12 Jan 2017 23:38:28 GMT

Hi Team -

I have run into a set analysis issue which I have never came across before. I have the following two tables:

Table:

LOAD * Inline [

Dim, Sales

A, 10

B, 20

C, 30

];

IslandTable:

LOAD * Inline [

ABC, XYZ

1ABC, 1

2ABC, 2

3ABC, 3

4ABC, 4

5ABC, 5

];

And lets say I have to use a pivot table using a dimension from the Island Table with set analysis on the Island table fields

1st Attempt (Doesn't work)

Dimensions: ABC, Dim

Expression: =Sum({<ABC = {'1ABC', '3ABC'}>}Sales)

2nd Attempt (Doesn't work)

Dimensions: ABC, Dim

Expression: =Sum({<XYZ = {1, 3}>}Sales)

In both the above two cases, I hoped to see two rows, but for some reason I saw all ABC

3rd Attempt (Works)

Dimensions: ABC, XYZ, Dim

Expression: =Sum({<ABC = {'1ABC', '3ABC'}>}Sales)

4th Attempt (Works)

Dimensions: ABC, XYZ, Dim

Expression: =Sum({<XYZ = {1, 3}>}Sales)

In my 3rd and 4th attempt, the set analysis starts to work as soon as I add XYZ as dimension (and XYZ is not collapsed)

Is this expected behavior?

swuehl‌, gwassenaar‌, mrkachhiaimp‌, johnw‌, troyansky‌, hic‌, jagan‌, vinieme12‌, petter-s‌, rwunderlich‌, marcowedel‌

Re: Data Island and Set Analysis

johnw — Fri, 13 Jan 2017 00:28:04 GMT

I'd have expected every one of them to have 5 rows with 60 on each row, which is what we get as long as the first dimension is collapsed on all of them. That much makes sense to me.

Set analysis is evaluated once per chart, not per row. So the first thing QlikView will do with any of these charts is "select" the values for either ABC or XYZ. It will then see which rows of Table are still available from which to sum Sales. All rows of Table are still available. And that's all that set analysis does directly, constrain the rows we use in our aggregation. It doesn't directly constrain the values of our dimensions. So the set analysis should have no effect on any of our results in any of these charts.

And yet it does. At least in charts 3 and 4. And ONLY if we expand them at least one level. So clearly I've misunderstood something, and the collapsed vs. expanded pivot table dimensions should be a big clue what it is.

Someone will surely explain it in a simple way that will make me smack my forehead, but nope, I don't get it.

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 00:48:57 GMT

John - I do follow your logic that set analysis is for expression and not for the dimension here, but its just weird that expanding the XYZ dimension resolves the issue.

I guess calculated dimension here might be way to approach this, right?

Re: Data Island and Set Analysis

johnw — Fri, 13 Jan 2017 01:03:27 GMT

Sure, or just a dimension since I have an irrational dislike of calculated dimensions.

if(match(XYZ,1,3),XYZ) as XYZ13

I do want to figure out what's going on here, though.

Re: Data Island and Set Analysis

nitin_01 — Fri, 13 Jan 2017 07:42:20 GMT

Hi Sunny,

Respect to Seniors,

So, What can we conclude from this? Should we use 'Calculated Dimension' or need to add one more dimension from unrelated tables to get the desired output in the chart?

Regards,

Nitin

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 10:12:43 GMT

Hi Nitin -

I don't think this discussion in concluded yet. I am still hoping to hear from some of the other experts.

Best,

Sunny

Re: Data Island and Set Analysis

petter — Fri, 13 Jan 2017 10:20:58 GMT

I looked into you test application Sunny. I find the first two cases (pivot tables) perfectly understandable as there will be a cartesian product between the fields of the two products. What I find hard to understand is why this doesn't happen with the last two pivot tables. Furthermore when clicking around in these two I got some odd behavior that didn't seem entirely consistent. I didn't take full notes and it is hard to reproduce just now. Briefly it was when I expanded and collapsed sometimes I got the full list of all five values in the ABC dimension whereas most of the time I got only two. That happened to both of the pivot tables. Unfortunately I couldn't find a way of reproduce it consistently...

I have recently - the last two years or so avoided using data islands. If I need to do something special in that direction I most often use ValueList() and ValueLoop() to calculate dimension values on-the-fly.

Re: Data Island and Set Analysis

marcus_sommer — Fri, 13 Jan 2017 11:13:53 GMT

I think the different behaviour between the collapsed/expanded pivot is caused through an ignoring of the set analysis condition within the collapsed state and by expanding the condition is evaluated against ABC.

Further I wouldn't consider it as inconsistent behaviour or as a bug because it made in my opinion no sense to calculate dimensions from isolated data-islands within one object - the base of them will be always a carthesic product and even quite small datasets could consume all available resources.

What was your origin aim of calculating over isolated data-areas?

- Marcus

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 11:56:19 GMT

So there were few things in action here

1) I was just wondering if we can remove the use of Pick(Match()) or If by using set analysis. So, here is an updated sample

Table:

LOAD * Inline [

Dim, Sales1, Sales2

A, 10, 20

B, 20, 30

C, 30, 90

];

IslandTable:

LOAD * Inline [

ABC, XYZ

1ABC, 1

2ABC, 2

3ABC, 3

4ABC, 4

5ABC, 5

];

and lets say that when ABC = 1ABC, I want to Sum(Sales1) and When ABC = 3ABC, I want to Sum(Sales2). This is very simple with pick match. But I was wondering if this could be done using set analysis.

=RangeSum(Sum({<ABC = {'1ABC'}>}Sales1), Sum({<ABC = {'3ABC'}>}Sales2))

2) I do need all ABC, but for this particular chart I just need just 2 of the 5 ABC. Again, Pick(Match()) or if would work, but a) I didn't think of them before I posted this questions, b) I wonder why it doesn't work

Re: Data Island and Set Analysis

vinieme12 — Fri, 13 Jan 2017 12:08:09 GMT

Hi Sunny try This

Sum({< ABC = {'1ABC', '3ABC'} >}Aggr(Sales,ABC,Dim))

Re: Data Island and Set Analysis

marcus_sommer — Fri, 13 Jan 2017 12:29:18 GMT

I don't think it could work with the data-island and set analysis and would prefer the pick(match()) approach - I assume you want to do something like this: Re: Dynamic expressions without Pick‌ but I believe without constructing this within the script - by linking ABC to Sales1 and Sales2 and creating with them Sales3 which respects the differences of the various fieldvalues of ABC - there is only pick(match()) or a similar lookup to solve this.

- Marcus

Re: Data Island and Set Analysis

vinieme12 — Fri, 13 Jan 2017 14:37:21 GMT

I just did an experiment! Loaded a table with no values "IslandTable2" associated with "IslandTable"

Table:
LOAD * Inline [
Dim, Sales
A, 10
B, 20
C, 30
];

IslandTable:
LOAD * Inline [
ABC
1ABC
2ABC
3ABC
4ABC
5ABC
];

IslandTable2:
LOAD * Inline [
ABC, XYZ
];

Dimension: ABC

Expression: Sum({< ABC = {'1ABC', '3ABC'} >} Sales)

Result below:

After adding XYZ from Table2 >> Now QV is trying to aggregate the Chart on ABC only

After Expanding >> QV Aggregate on ABC and XYZ

now Qlikview is able to apply the Set Condition, so It leads me to below assumption.

Assumption:

Qlikview automatically creates a temporary table but needs at least 2 dimensions from the Dimensions table to be able to do so.

How it does that is a mystery to me

PS: You can also try below calculated dimension and the same expression

=subfield(ABC&'_'&XYZ,'_',1)

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 14:39:09 GMT

I guess I do have a lot of work-around, now I am more curious as to why this behavior

Re: Data Island and Set Analysis

marcus_sommer — Fri, 13 Jan 2017 15:01:05 GMT

Here: Logical Inference and Aggregations is quite well explained what happens and one dimension will be enough to calculate an expression. I think in this case it's more how set analysis worked - similar to a sql where-clause - see: Why is it called Set Analysis?

- Marcus

Re: Data Island and Set Analysis

swuehl — Fri, 13 Jan 2017 15:33:51 GMT

This may be related to the issue described here:

Re: About ValueList

Attached a sample, have a look if the left two charts work at your site.

Funny facts:

Adding line breaks to an expression may change the results (see right two charts) 😉

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 15:43:31 GMT

They do work and it might be the best solution for me to work with. Thanks Stefan.

I see another strange thing (couldn't replicate this in my sample) that pivot table and straight table are showing the results differently

Same expression and no selections

-> Straight table just shows 1ABC

-> Pivot table shows all of the values under ABC

Do you guys see the same?

Re: Data Island and Set Analysis

vinieme12 — Fri, 13 Jan 2017 16:07:56 GMT

One dimension is enough but in this case there is no direct association so has QV taken all values to be possible values?

With swuehl‌ solution the set is applied to the ABC dimension using a Flag field that is available in the same table so its directly associated with it and we are not trying to apply set on a non-associated field.

I agree to what you said in the later part about the Set being executed like SQL statement ; but then won't we need some consolidated table to run a SQL query on? which why is why I inferred that there has to be a temp table .

Re: Data Island and Set Analysis

marcus_sommer — Fri, 13 Jan 2017 16:13:03 GMT

Yes you are right - there is a temp table regarding to the state vectors on which the calculations will be performed.

- Marcus

Re: Data Island and Set Analysis

marcus_sommer — Fri, 13 Jan 2017 16:19:04 GMT

Please post a short comparing between the "traditional" solution with pick(match()) and the flag-solution from Stefan - if you should really implement this approach to a real datamodel.

- Marcus

Re: Data Island and Set Analysis

sunny_talwar — Fri, 13 Jan 2017 16:23:14 GMT

marcus_sommer‌ - Apologizes for not understanding your question, but what exactly are you looking for me to post?