Using If/Else statement during LOAD

[#1]

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!

[#2]

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

[#3]

Roland, thank you very much,it worked!

But another problem has emerged...

This Code:

load

Customer_Shipper

,

sum

(Teus) as SUMTEUS

,

if

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

temnodic

resident

market_temp

group

by

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?!

[Miguel_Angel_Baeyens · Accepted solution]

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.

[#5]

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???

[Oleg_Troyansky]

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.

[#7]

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.