topic Re: Left join and isnull in QlikView

Left join and isnull

denwo2010 — Thu, 29 Aug 2013 15:03:37 GMT

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

Thu, 29 Aug 2013 15:05:55 GMT

Try this way:

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

Re: Left join and isnull

kaushiknsolanki — Thu, 29 Aug 2013 15:07:12 GMT

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

denwo2010 — Thu, 29 Aug 2013 15:09:52 GMT

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

Re: Left join and isnull

denwo2010 — Thu, 29 Aug 2013 15:10:51 GMT

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

Re: Re: Left join and isnull

kaushiknsolanki — Thu, 29 Aug 2013 15:12:56 GMT

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

Thu, 29 Aug 2013 15:15:02 GMT

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

denwo2010 — Thu, 29 Aug 2013 15:24:59 GMT

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

kaushiknsolanki — Thu, 29 Aug 2013 15:26:49 GMT

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

denwo2010 — Thu, 29 Aug 2013 15:35:38 GMT

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

kaushiknsolanki — Thu, 29 Aug 2013 15:38:11 GMT

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

denwo2010 — Thu, 29 Aug 2013 21:30:39 GMT

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