How to substitute a missing value with a valid value from between two fields?

[gino2780]

Hello, 

i need help for a substitution of values from one field if another field is empty (in the data load editor section):

Here is an example in hope my request is more understandable:

Field1 Field2 Proxy

-         534     534 

123    123      123

234    234     234

-         567     567

345      -        345

So basically the proxy field should contain the values of either of the field1 or field2 values.

Any ideas on how to solve this?

[rubenmarin · Accepted solution]

Hi, you can use the Coalesce() or the Alt() functions:

https://help.qlik.com/es-ES/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunc...

https://help.qlik.com/es-ES/sense/May2024/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunc...

LOAD Field1, Field2, Coalesce(Field1,Field2) as Proxy

[anat]

you may also try using if condition:

if(field2='' or len(trim(field2))=0 or isnull(field2),field1,field2) as Proxy

but you may face performance issue for huge amount of data.

other recommended options are  Coalesce() or the Alt() functions.

[Chanty4u]

Try this

    If(Len(Trim(Field1)) = 0, Field2, If(Len(Trim(Field2)) = 0, Field1, Field1)) as Proxy

[gino2780]

Hi Ruben, i got deeper into the coalesce and alt command. works perfectly well and is most efficient for such tasks. Thanks!