Left join and isnull

[denwo2010]

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

[#2]

Try this way:

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

[kaushiknsolanki]

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

[denwo2010]

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

[denwo2010]

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

[kaushiknsolanki]

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

[#7]

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...

[denwo2010]

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

[kaushiknsolanki]

Hi,

     You should use NullAsValue.

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

Regards,

Kaushik Solanki

[denwo2010]

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