topic Handling NULLS while Joining 2 tables in QlikView

Handling NULLS while Joining 2 tables

Anonymous — Fri, 15 Apr 2011 15:13:29 GMT

Hi,

I am working on 2 tables wherein I have join on one column.What my understanding and I can see is QlikView by default does the full outer join on two tables.

Now I have some records present nin Table 1 and not in table 2 and vice versa.

When I am checking the condition I can see there is null value for one particular record in table 1 for a value present in table 2. But I am not able to read this null value or handle this null value.

I have tried using NullasValue/ISNULL/NULLINTERPRET nothing is working in the way I am looking for.

I am looking for data in both table based on key but when key is not available in one of the table I have to setup some flag or like that.

Could you please help me on this.

Regards,

Swapnil

Handling NULLS while Joining 2 tables

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 15:21:46 GMT

Hello Swapnil,

It may be as easy as doing this in the script in both tables

If(Len(KeyField) = 0, 'Missing', KeyField) AS KeyField

So whenever the KeyField is a null value (its length is zero) the word "Missing" is used (or any other useful value for you) and you can easily check when that "Missing" records are loaded.

Hope that helps.

Handling NULLS while Joining 2 tables

Oleg_Troyansky — Fri, 15 Apr 2011 15:25:56 GMT

If you are trying to check for nulls within the JOIN LOAD statement, you won't find them - at that point, they might not exist yet (the value is missing, but it's not yet a null())

You can try reloading the same table using RESIDENT load, after it's already joined, and check for nulls then.

If you simply wanted to avoid mismatched records - maybe you can use LEFT or RIGHT or INNER join?

Handling NULLS while Joining 2 tables

Anonymous — Fri, 15 Apr 2011 15:30:03 GMT

Hi Miguel

Thanks for the quick reply.

This I have used to identify null values. But this function did not work when i tried retrieving the data and keep this function on joined table.

Also I am just thinking if I replace my say for e.g "Missing" when there is not value how the join will work. As I have join on the same column.

Regards,

Swapnil