Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

denwo2010
Contributor

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

11 Replies
Not applicable

Re: Left join and isnull

Try this way:

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

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

denwo2010
Contributor

Re: Left join and isnull

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

denwo2010
Contributor

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

Not applicable

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

denwo2010
Contributor

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

denwo2010
Contributor

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

Community Browser