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

[#1]

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 1	Header 2	Header 3
A		A
B		B
C	M	M
D		D
E	Z	Z

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

[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.

[#3]

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 · Accepted solution]

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]

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

[#7]

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!