Handling null values

[vijetas42]

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

[alexandros17]

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]

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.

[#4]

table1:

LOAD *

FROM

JOIN (table1)

LOAD *

FROM

finaltable:

NOCONCATENATE

LOAD

     ID,

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

     cat1

RESIDENT table1

DROP TABLE table1;

[JoaquinLazaro]

Hi  VijetaSharma

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

Joaquín