Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Populate a field with value from another field when value is null

Hi!

I know this is an easy one, but I cannot find a lean and fast way to obtain the following, if not by using a couple of JOIN and WHERE statements.

There has to be a faster way!

Header 1Header 2Header 3
A
A
B
B
CMM
D
D
EZZ

I would like to have what you can see in column 3.

Basically all values of column 2, and, when cell is empty, value of column1 should be filled in!

thank you

1 Solution

Accepted Solutions
its_anandrjs

Hi,

In a Load write script like

Temp:

Your sql load

Header1,Header2,Header3

From Source;

Data:

load *,

if(len(Header2) = 0, Header1,Header2) as Field3

Resident Temp;

DROP Table Temp;

HTH

Regards,

Anand

View solution in original post

6 Replies
Gysbert_Wassenaar

You can check for nulls with if(len(trim(MyFieldName))=0,..,...). If you have all the fields already in your table in becomes a simple if(len(trim(Field2))=0,Field3,Field2) to fill Field2 with the value of Field3 if there was no value in Field2.


talk is cheap, supply exceeds demand
Not applicable
Author

Dear Gysbert,

thank you for your answer.

But I'm sorry, my question was not 100% clear.

The table I was showing is a SQL table and I would like to reach a solution in the script, so that I have as output Field3.

thank you!

its_anandrjs

Hi,

In a Load write script like

Temp:

Your sql load

Header1,Header2,Header3

From Source;

Data:

load *,

if(len(Header2) = 0, Header1,Header2) as Field3

Resident Temp;

DROP Table Temp;

HTH

Regards,

Anand

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this script

LOAD

    *,

    If(Len(Header2) > 0, Header2, Header1) AS Header3;

LOAD * INLINE [

Header1,Header2

A,

B,

C, M

D,

E, Z

];

Hope this helps you.

Regards,

Jagan.

its_anandrjs

Hi,

Try this example

YourSqlLoad:

LOAD * inline

[

Header1,Header2

A,          

B,          

C,          M

D,          

E,          Z

];

Data:

load *,

if(len(Header2) = 0, Header1,Header2) as Header3

Resident Temp;

DROP Table Temp;

Regards,

Anand

Not applicable
Author

thank you for your answers.

I think yours, Jagan, is a bit inverted (First there should be the "LOAD INLINE" and then the if statement)

but in the end what really was missing to my script is the if statement, so you deserve a recognistion too!

I;  m just wondering, is using "0" [zero] a proepr way? woudln't be better to use somethign like IsNull?

thank you!