topic Handling null values in QlikView

Handling null values

vijetas42 — Thu, 06 Mar 2014 07:37:36 GMT

Hi everyone,

I am having data like,

table1:

ID, cat

1,aaa

2,bbb

3,ccc

4,ddd

and

table2:

ID, cat1

2,bbb

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

Re: Handling null values

alexandros17 — Thu, 06 Mar 2014 07:41:01 GMT

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;

Re: Handling null values

puttemans — Thu, 06 Mar 2014 07:46:27 GMT

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.

Re: Handling null values

Thu, 06 Mar 2014 07:49:22 GMT

table1:

LOAD *

FROM

JOIN (table1)

LOAD *

FROM

finaltable:

NOCONCATENATE

LOAD

ID,

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

cat1

RESIDENT table1

DROP TABLE table1;

Re: Handling null values

Joaquin_Lazaro — Thu, 06 Mar 2014 08:01:50 GMT

Hi VijetaSharma

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

Joaquín