Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.


1 Solution

Accepted Solutions
datanibbler
Champion
Champion

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

View solution in original post

5 Replies
datanibbler
Champion
Champion

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

Hy !

try this :

LOAD

Field_1,

Field_2,

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

FROM

...

maxgro
MVP
MVP

.....

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

Not applicable
Author

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