11 Replies Latest reply: Aug 29, 2013 5:30 PM by Denis Woods

# Left join and isnull

Hi, I have the following script LEFT JOIN  LOAD If(IsNull([PO Total])=-1,0,[PO Total]) as [PO Total],     [Order No]&'_'&[Order Line No] as [PO Key]     FROM D:\QlikView\02_QVD\po_shipping_aggr.qvd (qvd); DROP FIELDS [PO Key]; When I have the results back for the PO Total, the ones where there is no match it will show as '-' ? I want it to appear as 0 as I would expected from the abive If Isnull logic? Any ideas how to get round this? Thanks

• ###### Re: Left join and isnull

Try this way:

If(IsNull([PO Total]), 0, [PO Total]) as [PO Total]

• ###### Re: Left join and isnull

Hi, I have tried that also but still shows the missing values as '-' Thanks

• ###### Re: Left join and isnull

Maybe the problem isn't that you have a null, but you have an incorrect value on the table. Check the values of the field once you have loaded it, withouth making any transformation on it...

QV uses the '-' when cannot calculate something...

• ###### Re: Left join and isnull

Hi,

You can use the NullasValue variable to show the null into some value.

So the syntax will be.

NullAsValue A,B;

Set NullValue = '0';

Load A,B from x.csv;

Regards,

Kaushik Solanki

• ###### Re: Left join and isnull

Hi Kaushik Solanki, Not sure what you mean, how would I do this on my current script? Thanks

• ###### Re: Re: Left join and isnull

Hi,

Just put this at the start of your script.

NullAsValue [PO Total]

Set NullValue = '0';

Reload the app and see the change.

Regards,

Kaushik Solanki

• ###### Re: Left join and isnull

Hi Kaushik Solanki, I have tried this by putting at the start of script, but it does not seem to have done anything? Is it meant to be two different names NullAsValue & NullValue? Thanks

• ###### Re: Left join and isnull

Hi,

You should use NullAsValue.

If you want you can go to Help and get the details.

Regards,

Kaushik Solanki

• ###### Re: Left join and isnull

So I've put the below script before the start of the Load NullAsValue [PO Total Changes]; Set NullValue = '0'; Do I need to change my existing If Isnull formula? Should I be creating a variable on the "Variable Overview" ? Thanks

• ###### Re: Left join and isnull

Hi,

No I said put it before your load Statement, you can out in main table below the variables.

Not within the load statement.

Regards,

Kaushik Solanki

• ###### Re: Left join and isnull

I have put it at the start of script i.e. on the Main tab but still doesn't work?