How to return a value instead of "null" in a join ?

[#1]

Hi everyone,

I will try to clearly explain my case. In my qlikview model, there are 2 tables.  Table A linked to table B on key1.

On my qlikview, users can make selections on the fields of Table A so the link between the 2 tables looks like a "Table A left join Table B".

In my Qlikview application, there is a table which contains some fields of Table A and some of TableB.

But sometimes some records of key1(the key) in Table A are not found in Table B which means that all the fields from TableB return a null value.

How can I put a value "unknown" instead of null ?

Is it possible to do it without making an explicit join in the script ?

Hope my problem is clear enough, if not, do not hesitate.

Thanks & Have a good day

[ali_hijazi]

you can use the NullAsValue

NullAsValue A,B;

Set NullValue = 'your_desired_value' ;

Load A,B from x.csv;

so "your_desired_value" will be available instead of Null

the NullAsValue takes the names of the fields you want to have "your_desired_value" instead of Null

[datanibbler]

Hi Laura,

if I understand you correctly, you have like two boxes or a straight_table or something where the results from table_B are displayed based on the selection in table_A?

=> Can't you put an expression there using ISNULL() to display "unknown" or anything in case there is no value to be displayed?

HTH

Best regards,

DataNibbler

[#4]

I thought of it.The thing is that I use a table (and not a graph so I cannot create a dimension).

But i think I am going to create a table (with the graph).

[Peter_Cammaert]

And just to post from a different point of view:

One of the best practices in data management is to seek completeness at all times. This means that missing data (cf. unknown product IDs, missing customer groups etc.) will be marked with clear indicative values (like the one you suggest: 'Unknown') at load time. This can be pretty simple and is very helpful for end-users to interprete the presentated data in a correct way.

What to do?

To table B, add all missing IDs that are present in table A, and give them field values like '*MISSING*' or 'Unknown' or 'Unassigned'  Example:

CONCATENATE (Table_B)

LOAD ID_A as ID_B

     'Unknown' AS Field1,

     'Unknown' AS Field2,

      :

FROM Table_A

WHERE Not Exists(ID_B, ID_A);

and then do the JOIN.

Best,

Peter