topic Re: Re: Turning an IF Statement to Set Analysis in QlikView

Turning an IF Statement to Set Analysis

alexis — Fri, 14 Nov 2014 07:34:00 GMT

I have a very simple structure, as follows:

and I am achieving my objective of plotting a calendar dimension on the X-axis and the following expression on the x-axis:

= count(distinct if(StartDateInt <= DateID and EndDateInt >= DateID and Status = 3, SubscriberKey))

This is VERY SLOW so I am trying to define the expression using SET ANALYSIS

The following is my attempt which does not appear to work - ANY HELP WOULD BE APPRECIATED.

= count(distinct

[StartDateInt] = {"<=(DateID)"},

[EndDateInt] = {">=(DateID)"},

[Status] = {3}

SubscriberKey)

Re: Turning an IF Statement to Set Analysis

MK_QSL — Fri, 14 Nov 2014 07:36:18 GMT

Isn't it possible to create a Date Calendar for your fact table?

Using Iceland table and if together will always give you poor performance...

Re: Turning an IF Statement to Set Analysis

alexis — Fri, 14 Nov 2014 07:42:49 GMT

I agree with you 100% but as as I should have mentioned this is a simplified extract from a very large application that, for reasons that are too complex to explain here, the calendar needs to be disjointed from the fact table - after all this is why we have Set Analysis!

Thanks for taking the time to respond

Alexis

Re: Turning an IF Statement to Set Analysis

SergeyMak — Fri, 14 Nov 2014 07:54:38 GMT

Hi Alexis,

Did you select only one DateID? Because in your expression <=(DateID) if you have several DateID it will not work just because will be represented as <=(Only(DateID)) and if you have several of them you will get Null().

As an option you can try to use Max(DateID) instead of DateID.

Regards,

Sergey

Re: Turning an IF Statement to Set Analysis

alexis — Fri, 14 Nov 2014 15:02:37 GMT

Hi Sergey

Using the IF statement I get the right results irrespective of the range of dates selected.

Removing the DISTINCT speeds things up but I need to calculate DISTINCT so that is back in

I cannot see how I can write this (and to work) using SET ANALYSIS

best regards

Alexis

Re: Turning an IF Statement to Set Analysis

SergeyMak — Sat, 15 Nov 2014 11:38:39 GMT

Hi Alexis,

What is the working if statement? Could you show it?

And you didn't answer my question:

Did you select only one DateID?

Regards,

Sergey

Re: Turning an IF Statement to Set Analysis

alexis — Sat, 15 Nov 2014 15:05:40 GMT

Sergey

The working IF statement is in the question - here it is again:

count(distinct IF(StartDateInt <= DateID and EndDateInt >= DateID and Status = 3, SubscriberKey))

It works whether I select a year,a quarter, a month,a week or a date or date range

Alexis

Re: Turning an IF Statement to Set Analysis

morgankejerhag — Sat, 15 Nov 2014 18:10:42 GMT

Hi,

Is DateID a fixed value or something that depends on the transaction?

The problem with your lines like [StartDateInt] = {"<=(DateID)"}, is that "<=" is doing a search - not a selection.

if it is a fixed value you could for example use a dollar sign expansion

= $(='count(distinct

[StartDateInt] = {"<=' & DateID & '"},

[EndDateInt] = {">=' & DateID & '"},

[Status] = {3}

SubscriberKey)'

How is the data model? Is it possible to instead flag the correct lines in the script?

Re: Turning an IF Statement to Set Analysis

SergeyMak — Mon, 17 Nov 2014 01:16:16 GMT

Alexis,

As I understood, you need to calculate Amount of Subscribers in Year or Month or Day and show these numbers on the chart.

So, it's better to rebuild the data model and calculate with just simple Sum() or Count() without any IF or Set Analysis with the table like:

Date SubsKey Status

I can do it for you if you post a sample of your data.

Regards,

Sergey

Re: Re: Turning an IF Statement to Set Analysis

alexis — Mon, 17 Nov 2014 01:28:32 GMT

Hi Sergey

Here are a few things to help with the issue

a) I have a calendar table that needs to be separated from the fact table - this is too complex to explain but trust me that this is how it needs to be designed

b) I have a table (see Usage.qvd) which has a number of records - each record has a subscriber id, a start date an end date and the status for that period.

c) A subscriber therefore can have a number of records to reflect the various status that it has passed say from Active (Status 3) to Inactive (Status 1) and back Active for example

d) I need to build a chart that on the x axis has the date element (year, quarter, Month week etc) and on the y-axis number of DISTINCT subscribers that have been active (Status of 3) for the chosen period

Below are the 2 qvds you need

Re: Re: Turning an IF Statement to Set Analysis

alexis — Mon, 17 Nov 2014 01:32:00 GMT

Hi Morgan

As I explained this is a simplification of a small part of a large project - the data structure is as above - 2 tables (see data (Qvd files) that I attached for Sergey earlier), Calendar and Usage that need to be kept unassociated.

I need to build a chart that on the x axis has the date element (year, quarter, Month week etc) and on the y-axis number of DISTINCT subscribers that have been active (Status of 3) for the chosen period

Thanks

Alexis

Re: Re: Re: Turning an IF Statement to Set Analysis

SergeyMak — Mon, 17 Nov 2014 03:13:44 GMT

Alexis,

PFA my solution.

I think it's better to add additional calendar just to decrease data in the LinkTable.

I built the linkable for all Statues, but you can easily filter it and make it smaller if you need it only for Status=3.

Just reload the data from your qvd. I reduced the data in this file to load it here.

Regards,

Sergey

Re: Turning an IF Statement to Set Analysis

morgankejerhag — Mon, 17 Nov 2014 07:13:29 GMT

OK. So the two tables are not connected at all? How many rows do you have in each of the tables in the real case? Since they are not connected QlikView will generate a Cartesian product and consume a LOT of memory and cpu.

Since you have the calendar as X-axis the value of DateID will be different for each dimensional point. Set analysis however is calculated once for the whole set of dimensional values. You can not have a set analysis that takes the current dimensional value into account.

Without changing your data model I don't think you can solve this.