topic Re: Data Islands and Set Analysis in QlikView

Data Islands and Set Analysis

SRG — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

In my data model I have a date filed. I am using date Island to and compare against the date in my data model. Using 'IF' I am getting desired results. I need to compare two more conditions in my expressions to calculate sums. Use if is slowing down all the calculations. I am trying to see if I can use set expression with date Island.

The expression I am currently using is

sum(if([Date]=IslandDate,Type ='Dispute',ChkFlag='Yes',1)) . Can I obtain the same with set expressions.

Any help would be highly appreciated.

Re: Data Islands and Set Analysis

sunny_talwar — Mon, 17 Jun 2019 13:03:24 GMT

Not sure, but give this a shot

Sum({<[Date] = P(IslandDate), Type = {'Dispute'}, ChkFlag = {'Yes'}>} 1)

Re: Data Islands and Set Analysis

SRG — Mon, 17 Jun 2019 13:54:18 GMT

Thanks Sunny.

But the problem is it is giving me summarized results. If you see below the first measure is calculated using the if and the second measure is using set analysis. Using IF is slowing down the process completely.

Below are the expressions I used

Uisng IF: =sum(if([resolvedDate]=IslandDate,1))

Using Set Analysis: Sum({<[Date] = P(IslandDate),DateType= {'Resolved'}>}DataTypeCounter) .

Please let me if what I am doing is wrong.

Re: Data Islands and Set Analysis

marcus_sommer — Mon, 17 Jun 2019 14:52:41 GMT

I think in this way it would rather not working. In Qlik all relevant data must be associated. There are cases where it's useful to use an island-table to navigate between expressions/dimensions/objects or to use them as an independent selection or something similar - but this is usually done with a single (possible) value which creates a 1:n relationship.

A n:n relationship like indicated from your expression and the screenshot will create multiple large cartesian products which couldn't be handled with a set analysis else only with if-loops and they will be terrible slow. Therefore I suggest to rethink your datamodel.

- Marcus