If Statements, alternatives and performance

[#1]

Okay, this might be stupid but here goes.

I have a straight table, and one of the expressions is just an IF statement, that if some conditions are met, it shows a field, if they are not met, it say 'Not Set'. But as soon as I enable this expression it slows my chart right down. Now I am aware of the benefits of moving away from if statements and using say, set analysis instead. But Set Analysis only works in sum() and count() expressions right?

So the question is, is there an alternative to having the following expression in my chart, that would improve performance:

if(CONDITIONS, FIELD, 'Not Set')

(obviously the conditions and field have real values in my application.)

Thanks in advance

[alexandros17]

Is it possible to use that condition during loading of data in order to use only a field ?

[Anonymous]

you can try maxstring({<Conditions>} FIELD) if FIELD is a string

otherwise use max, but you will not get "Not Set".

Why don't you use the script to build the statement

Script

load

  A,

B,

if (Condition, Field,'Not Set') as NewField

Inthe Chart you just use NewField. I had significant performance success inmy customers project by using the script

instead in the chart.

[tresesco]

Set analysis and IF are not always equivalent. IF would execute row-wise in your chart, whereas Set Analysis would execute once for a chart (i.e. dimension-wise exceution won't take place there). If you need row-wise conditiona execution and improve performance(replace multiple IFs), try Pick(Match()) combination of expression.

[richard_pearce6]

Best to use fields created in the script that way you can often use set analysis

Sum({<ConditionField = {'1'}> FIELD)

Although if you're using an if statement as you've described about the performance overhead isn't as bad as it would be if you were using it within the calculation: Sum(if(CONDITIONS='1',Sum(FIELD)))

Richard

QlikCentral.Com

[#6]

Thanks to everyone for your replies!

In terms of loading it in my script, how would I go about mentioning other tables in the if statement? Can I do this as normal in the load?

So for example, would this work?:

tableA:

LOAD

     fieldA1,

     fieldA2,

     fieldA3

FROM...

tableB:

LOAD

     fieldB1,

     fieldB2,

     if(fieldB2 = tableA.fieldA1 AND fieldB3 > 1, fieldB3, 'Not Set') as NewField

FROM...

I'm assuming that tables are loaded sequentially not concurrently, so if this does work, the order of the tables would be of importance?

[#7]

Thanks a lot for the reply, I've marked your answer as helpful as you were the first to say this. But could you look at my reply to Rudolf below as there's a bit more to this!

Thanks a lot!

[richard_pearce6]

Hi,

You could do that if you were doing a proceeding load or a concatenate

Richard

[#9]

Sorry, I really am new to this. Could you elaborate a bit?

To clarify, the script loads the information from an XML source, into QVD files and I would be then creating the field as Qlikview loads from the QVD files into the application. Is this what you mean by a preceding load?

[alexandros17]

No, what you have written cannot work, give us the logical link among tables ...