topic Re: if vs set in QlikView

if vs set

Tue, 03 Feb 2015 08:12:57 GMT

Hi, I have read in various threads that set should be faster and give better performance than if.

I have a case where I just need to say if FieldA = 'x value' then for instance multiply field B, field C and Field D.

It seems silly to make a set for all fields, so I would expect an if solution to be the choice?

Re: if vs set

Michiel_QV_Fan — Tue, 03 Feb 2015 08:15:45 GMT

Can you elaborate a bit more. Set is used for creating variables and if is a control statement. What do you mean by set in relation to your question?

Re: if vs set

Peter_Cammaert — Tue, 03 Feb 2015 09:08:32 GMT

I think he means Set Analysis and the IF() function.

Set Analysis and IF() are not always interchangeable. Set Analysis will reduce the data that goes into your expressions (in a very efficient way) while IF() may do different things depending on what data is offered. An IF() with an else parameter won't reduce the data, however.

Re: if vs set

Tue, 03 Feb 2015 09:35:07 GMT

oh sorry, as Peter assume, yes I do mean set analysis vs. if() .

Also I forgot to ask how ?

if([Field A] = 'text', sum([field B]/sum([field C]/[fieldD]))) does not work.

Re: if vs set

Peter_Cammaert — Tue, 03 Feb 2015 09:46:22 GMT

Let's do this step by step.

First, why isn't it working? Is [Field A] a dimension in your table? Do the values [Field C] and [Field D] belong to the same row?

In expressions, it is important whether you place an IF() inside or ouside the aggregation functions. To figure out what will work, we need more information about your Data Model. Can you supply some?

Re: if vs set

Tue, 03 Feb 2015 10:50:43 GMT

thanks, I dont think that I am allowed to share details, but I will try to describe. I had to change it a bit and insert a variable (which again build on a set). I have a star model, they should be connected.

if([Field dimA] = 'text', (sum([Field fact A]/([$(variable1)])/sum([Field dimB])))

The result is '-'

Re: if vs set

jonathandienst — Tue, 03 Feb 2015 11:11:26 GMT

How is 'variable1' constructed and what does it look like?

Re: if vs set

Wed, 04 Feb 2015 08:29:59 GMT

The variable is just a similiar If/set statement, though in the long it will have multiple criteria, so I put it in a variable to make it simpler. I have replaced it with a fixed number to be sure that is not the problem.

So the overall formula is (X/Y)/Z. Where Y at the moment is set to 200500100.

So if([Field dimA] = 'text', (sum([Field fact A]/200500100)/sum([Field dimB]))), they are from different tables connected indirectly via the keys.

In Excel the criteria/if/set formula could look like this for X could look like this =SUMIF(C:C;"=text";E:E). So I just want to search in one field, and if criteria is true, then sum correspondent rows in another field.

Further more I have tried Aggr, but I have got any sense out of it so far.

Re: if vs set

rajeshvaswani77 — Wed, 04 Feb 2015 08:51:22 GMT

The set would be more efficient than if in case of large data.

thanks,

Rajesh Vaswani

Re: if vs set

Wed, 04 Feb 2015 12:40:30 GMT

Thanks, I got it to work with set analysis, though the number it extremely low, from what to expect.

The set version without variable goes like this:

=(Sum({$<[Field dimA] = {'text'}>} [Field fact A])

/sum({$<[Field A] -= {'text2'}>} [Field fact B])) //this is the variable from above = 200500100

)/Sum(Sum({$<[Field dimA] = {'text'}>} [Field dimB])

How would it look like as an if formula?

Re: if vs set

rajeshvaswani77 — Thu, 05 Feb 2015 05:59:37 GMT

Hi Mikael,

It would be like this.

thanks,

Rajesh Vaswani

Re: if vs set

maksim_senin — Thu, 05 Feb 2015 07:27:31 GMT

Hi,

It's really difficult to understand the root cause of the issue, just one suggestion:

1. try break down your expression to elementary elements and show the in a table

2. add set analysis to each element and look whether they (a) calculated without errors and (b) satisfy your expectations

Best regards,

Maxim

Re: if vs set

Thu, 05 Feb 2015 15:07:18 GMT

thx, yes the set version works but would it make sense in an if analysis?

Re: if vs set

Thu, 05 Feb 2015 15:09:54 GMT

thx, and sorry I see it became a bit messi. I should have started with the set analysis and then ask how to make it with If analysis, as I just did in response to Rajesh.