topic Re: Writing 'If Statement' within the Load clause in QlikView

Writing 'If Statement' within the Load clause

Fri, 16 Sep 2011 15:25:14 GMT

Hello,

Does writing 'If Statements' within the load clause in the script cause a significant slow down in the load speeds in you opinion.

I know it will depend in the number of 'ifs' and the number of records you are loading but has anyone ever had any issues with performance with this?

Interested to hear your experiences. Many thanks.

Dave

Re: Writing 'If Statement' within the Load clause

IAMDV — Fri, 16 Sep 2011 16:11:30 GMT

Hi Dave,

Good question. Yes, I have used IF statement in 30 million rows document. I have noticed that it takes more time if you have nested if's. However, I have eventually changed IF statement to ApplyMap with a Mapping Load.

I hope this helps.

Cheers - DV

Re: Writing 'If Statement' within the Load clause

perumal_41 — Sat, 17 Sep 2011 06:41:29 GMT

Hi,

Please check this method

Map_1:

MAPPING LOAD

test,

'Yes'

RESIDENT table1;

New_Table:

LOAD

test,

ApplyMap('Map_1',test,'b') AS test1

RESIDENT table2;

That should do it.

Regards ,

Perumal.A

Writing 'If Statement' within the Load clause

swuehl — Sat, 17 Sep 2011 11:21:45 GMT

Hi David,

is your question about how to replace if()'s? Well, if you are using if's just to do kind of mapping, the already proposed mapping tables might be a solution if you are checking for equality on a single field.

If your question is more of an abstract kind, you will need to clarify what you understand under the term performance "issue". If an issue is that your load time may break a business requirement, than I would guess that many developers have experienced that kind of issue during development, but probably handled successfully by redesigning the load (maybe using some other techniques like mapping tables) and / or the reporting architecture (i.e. maybe using somekind of stages to prepare qvd's that can be reused and loaded optimized from within multiple qvw's) or using incremental load or...

So a solution to an "issue" is IMHO depending on your environment (technical and business).

If your question is more about some comparative measures about using if's or don't use them in load, well I don't have data at hand. I would also guess that it is not only depending on number of if's and number of records, but also on type of comparison (i.e. string or numerical).

But if you can do the data transformation using an if or similar technique in the script, I would always prefer this over doing same in the front end, even if your load time increases.

Not sure if this helps, but have a nice weekend

Stefan

Writing 'If Statement' within the Load clause

Wed, 21 Sep 2011 13:12:35 GMT

Thanks a lot for the info DV. I'll look into using the ApplyMap function. It's something I wasn't aware of.

Dave.

Writing 'If Statement' within the Load clause

Wed, 21 Sep 2011 13:18:21 GMT

Thanks Perumal. That's interesting to know about. I will look into using that method in future.

Dave.

Writing 'If Statement' within the Load clause

Wed, 21 Sep 2011 13:31:42 GMT

Thanks Stefan. Yes, it was a vague question. I was just looking to see generally if the use of 'Ifs' in the script had a drastic effect on load time. You raise some interesting points there about the context of your application and the 'acceptable' performance. Thanks for the information and food for thought.

Dave.