topic Re: How to create a new field using if/then from 2 existing fields in QlikView

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

Wed, 03 Sep 2014 13:52:01 GMT

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.

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

datanibbler — Wed, 03 Sep 2014 14:00:35 GMT

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

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

maxime66 — Wed, 03 Sep 2014 14:08:37 GMT

Hy !

try this :

LOAD

Field_1,

Field_2,

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

FROM

...

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

maxgro — Wed, 03 Sep 2014 16:42:49 GMT

.....

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

......

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

MarcoWedel — Wed, 03 Sep 2014 17:32:07 GMT

If your fields are numeric, try

Alt(Field_1, Field_2) as Field_3

hope this helps

regards

Marco

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

Wed, 03 Sep 2014 21:15:31 GMT

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