Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
saradhi_it
Contributor II

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

ABCD
123221-321
1242220322
02230323
126224-324
127-234325
0011326
---327
-0-328

Regards

Pardhu

Tags (2)
9 Replies
Highlighted

Re: how to use null in the IF condition

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

Highlighted
saradhi_it
Contributor II

Re: how to use null in the IF condition

nope all are separate tables 

Highlighted

Re: how to use null in the IF condition

provide more details.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

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

Highlighted

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

Highlighted
qlikview979
Valued Contributor

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;

Highlighted
kkkumar82
Valued Contributor III

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

Highlighted
vsudhakar
Contributor III

Re: how to use null in the IF condition

alt or if condition the to solve the problem

Highlighted
MVP & Luminary
MVP & Luminary

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.