9 Replies Latest reply: May 15, 2016 1:35 PM by jagan mohan rao appala

# how to use null in the IF condition

Hi

i have four  columns (A,B,C,D) in the same table  which contains zeros and null values in the A,B,C (tables) i need to replace only  null

value in ABC columns from D column(any value)

i think i need to use IF condition to replace the null values

 A B C D 123 221 - 321 124 222 0 322 0 223 0 323 126 224 - 324 127 - 234 325 0 0 11 326 - - - 327 - 0 - 328

Regards

Pardhu

• ###### Re: how to use null in the IF condition

A, B , C D are tables or fields in one Table? it's confusing

• ###### Re: how to use null in the IF condition

nope all are separate tables

• ###### Re: how to use null in the IF condition

You need to replace the null values in A,B,C from table D??

if so try like this

if(len(trim(A))=0,D,A) as A,

if(len(trim(B))=0,D,B) as B,

if(len(trim(C))=0,D,C) as C

• ###### Re: how to use null in the IF condition

This might also work:

LOAD Alt(A, D) as A,

Alt(B, D) as D,

Alt(C, D) as C,

D

FROM...

• ###### Re: how to use null in the IF condition

provide more details.

Regards

• ###### Re: how to use null in the IF condition

Hi Saradhi,

Try like this

T1:

load * Inline [

A, B, C, D

123 ,221, -, 321

124 ,222, 0, 322

0 ,223, 0, 323

126 ,224, -, 324

127,  -,    234,325

0 ,0 ,11 ,326

- ,- ,-, 327

-, 0, -, 328

];

NoConcatenate

T2:

load

Alt(A, D) as A,

Alt(B, D) as B,

Alt(C, D) as C,

D

Resident T1;

DROP Table T1;

• ###### Re: how to use null in the IF condition

Just an addition to others

if(isnull(A),D,A) as A

Similarly to all columns

This will work only if the qvd contains the null values , if it contains spaces then as others suggested

you should take if(len(Trim(field))=0,D,A) as A

• ###### Re: how to use null in the IF condition

alt or if condition the to solve the problem

• ###### Re: how to use null in the IF condition

Hi,

You can also try Alt() like below, it will return second parameter value if first parameter value is null.

TableName:

LOAD Alt(A, D) as A,

Alt(B, D) as B,

Alt(C, D) as C,

D

FROM DataSource;

Hope this helps you.

Regards,

Jagan.