How to create a new field using if/then from 2 existing fields

[#1]

I have 2 fields, Field_1 and Field_2.  I'm trying to create (hopefully in the script as it would carry through the entire file, but if no,t then in an expression) a third field that says something to the effect of "populate this with Field_1 if there is a value, otherwise populate with Field_2" and name this new one Field_3.

[datanibbler · Accepted solution]

Hi Barbara,

just try. The scripting_language of QlikView is pretty close to plain English.

Try

>> IF(LEN([Field1])>=1, [Field1], [Field2]) as Field3 <<

Empty fields are somewhat tricky - to find out whether the field is populated or empty, try any of the following

- ISNUM() (if the value that should  be there would be numeric)

- ISTEXT() (if the value supposed to be there would be a text string)

- LEN([Field1])

- ISNULL([Field1])

It really depends - on what, I cannot quite say 😉 I just always try which one of those formulas works.

HTH

Best regards,

DataNibbler

[maxime66]

Hy !

try this :

LOAD

Field_1,

Field_2,

if(not isnull(Field_1) ,Field_1,Field_2) as Field_3

FROM

...

[maxgro]

.....

if(len(trim(Field_1))>0, Field_1 , Field_2) as Field_3,

......

[MarcoWedel]

If your fields are numeric, try

Alt(Field_1, Field_2) as Field_3

hope this helps

regards

Marco

[#6]

Thank you!  I tried yours first and it worked perfectly.