topic Re: If Statements in script in QlikView

If Statements in script

Anonymous — Sat, 04 Nov 2017 00:00:46 GMT

I have a requirement where I'm using the following If statement in the load script. I'd like to know if anyone has any suggestions to be more efficient. Or is this the best way to do this? Note that I have over 8 million records in this table. Thanks in advance.

If( (practice = 'A' or practice = 'B') AND (Payer='1') AND ServiceDate < '01/01/2000', 'code1',
If( v75_svcSortName = 'C', 'code2',

If(( practice ='A' or practice = 'B' or practice =’D’) AND (Payer ='2') AND ServiceDate < '01/01/2005', 'code3', ‘other’ ))) AS BillingCode

Re: If Statements in script

Anil_Babu_Samineni — Sat, 04 Nov 2017 02:46:21 GMT

THis one work line charm without hang application. So, you can check without this condition and with condition how elapse time increases

Re: If Statements in script

Anonymous — Mon, 06 Nov 2017 16:47:01 GMT

Can you please tell me if there another way to do instead of using 'If' statements?

Re: If Statements in script

Anil_Babu_Samineni — Mon, 06 Nov 2017 16:48:37 GMT

Pick(Match()).. is alternative option. But, I don't think any performance issue using yours.

Re: If Statements in script

Anonymous — Mon, 06 Nov 2017 16:52:06 GMT

How can I do Pick(Match) for several fields with several conditions?

practice, Payer, v75_svcSortName, and ServiceDate must be < Date

Re: If Statements in script

maxgro — Mon, 06 Nov 2017 16:55:31 GMT

Don't think

Also I think that without the if the running time would decrease for a few seconds,

did you try without if?

Re: If Statements in script

Anil_Babu_Samineni — Mon, 06 Nov 2017 16:57:07 GMT

My question is "Why are you looking another approach". Because, If else statement are good in Script.

Re: If Statements in script

Anonymous — Mon, 06 Nov 2017 17:02:54 GMT

If I take the 'If' Statement out then I'll need to put into the set expression. I'm just trying to make the script more efficient as I understand 'If' Statements can be costly within the script.

Re: If Statements in script

Colin-Albert — Mon, 06 Nov 2017 17:10:23 GMT

Do you need to run the if and compare to all 8M records to calculate the BillingCode for each row everytime you load the data?

Why not look at an incremental load, where you run this script just against new or modified data and store the results to a QVD, then you are only calculating the Billing Code for new data not on all 8M records each time.

Search for "Incremental Load" and you will find many resources with example scripts.

Re: If Statements in script

Peter_Cammaert — Mon, 06 Nov 2017 17:16:08 GMT

[Edit: Removed useless example of non-existent optimization]

But as Massimo and Anil have already stated, you will make an effort without really knowing what your gain could possibly be. To get an idea of how much time can be save ideally, run the script without the IF()'s and then with the IF()'s and compare those delays. Contrary to what you are thinking, the load script doesn't mind that it has to run a few minutes longer. On the other hand, using nested IF()'s in the User Interface should be avoided as much as possible.

Re: If Statements in script

Anonymous — Mon, 06 Nov 2017 17:21:20 GMT

Thank you for the additional information about the sequential order in the If statements. I will try that along with also testing the performance time with and without the If statements.

This 'If' statement is part of code that I use to create a QVD daily for all 8 million rows. I know how to do incremental loads, but I'm not sure that in this case that an incremental load will benefit.

Thanks everyone for your input.

Re: If Statements in script

maxgro — Mon, 06 Nov 2017 17:26:37 GMT

Peter, AFAIK short circuit evaluation doesn't happen in Qlik

Re: help - performance improvements

Re: If Statements in script

Peter_Cammaert — Mon, 06 Nov 2017 23:23:51 GMT

I must have been in a hurry to leave the office when writing that post. While driving home, I got all sorts of stinging thoughts about why that reasoning couldn't be correct. And when I got home I had a couple of strategies to make it work, in scripting at least.

I can't find a reason why this kind of optimization can't be used in scripting (even with those weird IF() functions). And the thread you are linking to only discusses valid reasons why it won't work in charts.

But... no it doesn't work in scripts either. The improvement from 2014 still hasn't happened.

Thanks for putting the record straight.