Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vijetasharma
Contributor III

Handling null values

Hi everyone,

I am having data like,

table1:

ID, cat

1,aaa

2,bbb

3,ccc

4,ddd

and

table2:

ID, cat1

1,

2,bbb

3,

4,

but,when i am joining these two table it gives me output like,

ID,cat cat1

1,aaa,-

2,bbb,bbb

3,ccc-

4,ddd,-

and I want output like

ID,cat,cat1

1,aaa,aaa

2,bbb,bbb

3,ccc,ccc

4,ddd,ddd

how I will achieve this output

4 Replies

Re: Handling null values

After that tou joined the tab for example in this way:

Tab:

load * resident tab1;

join

load * resident tab2;

Read it again in this way and add a control

FinalTab:

noconcatenate

load ID, if(isnull(cat), cat1, cat) as cat,

load ID, if(isnull(cat1), cat, cat1) as cat1

resident Tab;

drop table Tab;

puttemans
Valued Contributor

Re: Handling null values

If I follow you correctly, then you want to keep the value of cat1 where it exists, but if there is no value, you want the value of cat. This can be done as followed:

If you load the full table after the join and add the following line:

IF (cat1 = '-',cat,cat1) as catnew;

and then after checking, in a new load or a preceding load you drop the old cat1 and rename catnew into cat1, you'd get what you need.

Not applicable

Re: Handling null values

table1:

LOAD *

FROM

JOIN (table1)

LOAD *

FROM

finaltable:

NOCONCATENATE

LOAD

     ID,

     if(isnull(cat),cat1,cat) as cat,

     cat1

RESIDENT table1

DROP TABLE table1;

joaquinlr
Valued Contributor II

Re: Handling null values

Hi  VijetaSharma

Read this document published here by HIC, is very interesting.

Joaquín

Community Browser