Data Island and Set Analysis

[sunny_talwar]

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‌

[marcus_sommer]

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

[vinieme12]

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)

[sunny_talwar]

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

[marcus_sommer]

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

[swuehl]

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) 😉

[sunny_talwar]

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?

[vinieme12]

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 .

[marcus_sommer]

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

- Marcus

[marcus_sommer]

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

[sunny_talwar]

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