topic Re: How to Join nullable ID to not nullable ID in other table in QlikView

How to Join nullable ID to not nullable ID in other table

hugmarcel — Thu, 30 Jan 2014 16:45:02 GMT

I have 2 tables, one contains field ID which can be nullable (have empty values). I need to join the table to another one containing field ID, where ID is never empty.

NULL value can join with any ID in table 2.

How can this be achieved?

Thx- Marcel

Re: How to Join nullable ID to not nullable ID in other table

Thu, 30 Jan 2014 16:49:41 GMT

hey Marcel,

Question how can null be joint with any ID(is it totally random/ you mean it applies for all other valid IDs).

For example

ID1
1
2
3
-
-

ID2
1
2
3
4
5
6
7

Can you explain how you want it in the above case. Does 4th and 5th row in first table1 apply for all IDs in Table2 ?

Thanks

Re: How to Join nullable ID to not nullable ID in other table

hugmarcel — Thu, 30 Jan 2014 16:50:16 GMT

yes

Re: How to Join nullable ID to not nullable ID in other table

MK_QSL — Thu, 30 Jan 2014 16:50:59 GMT

If there is only one ID matching between two tables, it is impossible to get merged data.

You can either Load Nullable table with

Load * From TableName where NOT ISNULL(ID);

Load *, IF(ISNULL(DI), 'UNKNOWN',ID) as ID from TableName;

Re: How to Join nullable ID to not nullable ID in other table

hugmarcel — Thu, 30 Jan 2014 16:59:45 GMT

I do not want to loose the empty IDs of table one.

"UNKNOWN" value does not help as table 2 does not contain value UNKNOWN.

Goal is just not to loose the empty IDs in table one when joining.

- Shall I add an artificial row having value UNKNOWN in table2?

- Or is there means with outer joining or something?

thx - Marcel

Re: How to Join nullable ID to not nullable ID in other table

MK_QSL — Thu, 30 Jan 2014 17:04:44 GMT

Better to add one more line as UNKNOWN is another table...

Re: How to Join nullable ID to not nullable ID in other table

maxgro — Thu, 30 Jan 2014 18:02:32 GMT

perhaps I don't understand but if you join 2 tables you don't lose nulls

T1:

load if(ID='-', null(), ID) as ID, 'T1-' & ID as COL1;

LOAD * INLINE [

];

T2:

LOAD *, 'T2-' & ID as COL2;

LOAD * INLINE [

];

DROP Field COL1, COL2;

Table:

NoConcatenate load * Resident T2;

join (Table) LOAD * Resident T1;

DROP Table T1, T2;

Re: Re: How to Join nullable ID to not nullable ID in other table

Thu, 30 Jan 2014 18:44:26 GMT

I think I understood what you are trying to do.Lets say:

Table1:

ID	Country
1	US
2	US
3	UK
4	UK
5	China
-	Mexico
-	Canada

Table2:

ID	Amount
1	23
2	45
3	78.8
4	56
5	33.3

Result:

Check out the attached file. Hope it helps.