topic Using If/Else statement during LOAD in QlikView

Using If/Else statement during LOAD

Wed, 12 Jan 2011 17:15:18 GMT

Greetings!

I have searched for this as much as I could in the QV documentation, but found no info...

What is the syntax for an IF statement during a LOAD?

Here is what I'm trying to do:

load

*,
if (([Partner Code]='' || [Partner Code] || 'NOT FOUND' || LEN(TRIM([Partner Code]))=0,'No'),Expression-1,'Yes')
as result
resident market_temp;

Any thoughts???

Thanx 1 more time!

Using If/Else statement during LOAD

Wed, 12 Jan 2011 17:30:18 GMT

Hello Thiago,

a first guess would be:

if( [Partner Code] = 'NOT FOUND' OR LEN(TRIM([Partner Code]))=0, 'no', 'yes')

which means with len(...)=0 you should not need the first part. The return value for the then-case is after the first comma (='no'), else case after second comma (='yes'). I didn't recognize what to do with "Expression-1" but the term above should lead you into the right direction.

Regards, Roland

Using If/Else statement during LOAD

Wed, 12 Jan 2011 19:22:12 GMT

Roland, thank you very much,it worked!

But another problem has emerged...

This Code:

load

Customer_Shipper

sum

(Teus) as SUMTEUS

( [Partner Code] = 'NOT FOUND' OR LEN(TRIM([Partner Code]))=0, 'no', 'yes') as

temnodic

resident

market_temp

group

Customer_Shipper;

drop

table

market_temp;

Is generating this error:

And I cant figure out whats wrong in the syntax (due to the GROUP BY clause demanded by the SUM())..

any light?!

Using If/Else statement during LOAD

Miguel_Angel_Baeyens — Wed, 12 Jan 2011 19:33:36 GMT

Hello Thiago,

It seems that you have your If() function in two lines, which is not valid

if([Partner Code] = 'NOT FOUND' OR LEN(TRIM([Partner Code])) = 0, 'no', 'yes') as temnodic

This should work.

Hope that helps.

Using If/Else statement during LOAD

Wed, 12 Jan 2011 20:09:59 GMT

Hello Miguel,

Thx for asnwering again!

I tried it out, but there's something wrong with the SUM() function and the GROUP BY clause... qhen I comment both lines, the code runs fine...

Any thoughts???

Using If/Else statement during LOAD

Oleg_Troyansky — Wed, 12 Jan 2011 21:20:35 GMT

The problem is that your GROUP BY load requires that all the fields are either GROUP BY keys, or calculated as aggregations. In your case, the IF statement is using the fields that are not in your GROUP BY keys.

You need to either declare PARTNER_CODE as your GROUP BY key, or enclose it in aggregation function, like MaxString(PARTNER_CODE) - then it will work.

Using If/Else statement during LOAD

Thu, 13 Jan 2011 12:46:44 GMT

Miguel/Oleg,

Thank you very much, your answers combined brought me the expected results.

I beg pardon for basic questions, but unfortunatelly, QV development lacks in step-by-step tutorials, books, etc... Let's hope it changes soon!

Owe you guys another one.

My best wishes.