Handling NULLS while Joining 2 tables

[Anonymous]

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

[Miguel_Angel_Baeyens]

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.

[Oleg_Troyansky]

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?

[Anonymous]

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