replace missing values by numeric values ( not blank values )

[hillrunner]

Hello

I know this question is often asked, I saw 20+ posts about this.

But that's weird, i have a field which come from a left join and so there are some missing values

i wanted to make them replaced by numeric or chains values with for example

if(isnull...) or if(len(trim))=0,'replacedMissingValues''Field')...

But these kind of functions work if it is null values but doesn't work when it is missing values from join. 

On other posts, people suggest to use " not show if null values ".

or do Properties->Presentation->Missing Symbol(change to Zero).

But me, i need to keep lines when there are missing values on this field, i want to convert these missing values to numeric values but not just as symbol, i want a real 3 for example and not just a symbol of 3.

In case I didn't explain really well ( i think that's the case ), my current function of loading is

if([PI présente]='OK','OK',if([PI présente]='a sup car doc diff','a sup','missing value')) as [PI présente corrigée],*;

So if the field [PI présente] is not 'OK' or if the field is not ' a sup car donc diff', i will have the value 'sdsd'. I thought missing values from [PI présente] will become 'missing value' in [PI présente corrigée] but no 🙂

* and also the alt function doesn't work 😕 *

If you have an idea that would be awesome !

thanks for reading

[Or]

It's kind of the long way around, but...

myTable:

Load * from Table1;

Left Join

Load * from Table2;

myFinalTable:

Load *, if([PI présente]='OK','OK',if([PI présente]='a sup car doc diff','a sup','missing value')) as [PI présente corrigée]

Resident myTable;

Drop Table myTable;

If it's just the one field, consider using a mapping load instead. This will allow you to automatically fill in the missing values.

[hillrunner]

thanks for response 🙂 !

I think it's me who didn't understand well your solution due to my bad knowledge in qlik but that's a join and the load as I did it i think ? And the missing values are still here 

To make better understand my problem

this is my fields and I would like that when on the column PI présente ( the first one ), it is a missing value ( due to the join ), that this missing value be replaced by a chain or numeric value in the column PI présente corrigée field. 

That's because i would like to delete all the lines which has values " a sup doc diff" in the first column by replacing them by null values and then not displaying  them with the option " not display null values ".
But i would like to keep the lines which have already  missing values on the " PI présente" column ( and if I use the option " not display null values" it will not show these ones )

Hope i explained it well 🙂

[Or · Accepted solution]

The solution is to load, join, and then do a second load in which you replace the missing values. You then discard the original load table and only keep the second, fixed table. This should cover the situation you described (missing values because of a join).