topic Joins in QlikView

Joins

Fri, 13 May 2011 04:18:07 GMT

Hi,

During the outer join for the rows which does not have the mtaching rows are displayed as '-' . i want to replace - with 0. i tried with if condition as below

but it is not working .please suggest

if(a='-' ,0 ,a )

Re: Joins

Fri, 13 May 2011 04:38:14 GMT

The '-' is not actually a '-' it is a null value. The '-' is the default product of a null value. If you don't need it for math reasons you can just change the properties of the table to display '0' instead of '-'.

Use whatever null expression you want to and replace with '0'.... IF( ISNULL(a),0,a) or something similar.

This image is if you want it to just show '0' and not actually be '0' (Saves memory if you are working with a lot of records - in one of my apps we had 1M+ records but a field was only populated for about 30k of them. We used this method rather than allocating a '0' value to the other 970,000 records)

Re: Joins

Fri, 13 May 2011 04:56:50 GMT

Hi,

Thanks for your response i am trying at the Script level. And i havce tried the NUllasvalue, set null interpret but it is not working. I am loading the data from excel. Please suggest solution.

But i noticed if i am chooing the option to not to display null values and the rows which has '-' is not displayed but at script level it is not working Why?

Re: Joins

Fri, 13 May 2011 05:04:32 GMT

The exact code we use at the script for one of our applications is:

If( IsNull( [Field A]),'0',[Field A]) AS [Field A],

Regarding the display of Null Values, this will only suppress Null values in the dimension you select it for, not the line items attributed to it. If you want to suppress lines with null or 0 values, go to the presenation tab and select 'Suppress Zero Values'.

Perhaps if you can load a sample QVW if you have further trouble.

Re: Joins

rbecher — Fri, 13 May 2011 05:34:35 GMT

HI,

have you tried to use in script:

SET NullValue='0';

NullAsValue a;

- Ralf

Re: Joins

Fri, 13 May 2011 05:46:44 GMT

yes ralf i have tried

Since i am connecting to excel through table file Set NullValue will not work but still itried that .

Nullinterpret is working but not for all values some of are displaying correctly but some of them are not.

Joins

Fri, 13 May 2011 06:00:53 GMT

For the Full Outer join it is not working properly.

Joins

Fri, 13 May 2011 06:03:03 GMT

Scramble the data and load a sample qvw.

Joins

Fri, 13 May 2011 06:29:57 GMT

There are two possibilities as per your situation.

1. Null values in the source - In this case you can replace the null values in the script level.

2. Null values in the presentation layer - This happens when you use columns from 2 qlikview tables in the presentation layer. in the background, qlikview outerjoins these tables and if there are no matching values for the joining column, values will be displayed with null ('-'). In this case you canont handle this in the script level. You have to handle this in the presentation layer itself by changing the default value for null from '-' to 0

Joins

Fri, 13 May 2011 07:13:59 GMT

Hi,

I agree with you.But after storing the data in the QlikView table u should be able to handle it. Since all the data will be in a single table.

Please correct me if i am wrong

Joins

Fri, 13 May 2011 08:27:11 GMT

If I assume that you are having 2 qlikview tables A and B, adn you are if you are replacing null values in the edit script, you will not have null values after loading data into qlikview. And if Qlikview is doing an outerjoin for presenting the data for you in an object, there can still be null values represented by '-' if there are mismatching records as per the joining column. These null values you can only handle as per the reply above by Kieran Boyce with screenshot.

Joins

Fri, 13 May 2011 10:10:40 GMT

Thanks for everyone,

Yes i was doing outerjoin and the missing values were need to converted to 0 and do the aggregation,

So i used the same if condition in the resident table and it is working fine

Joins

schivet — Fri, 13 May 2011 11:00:31 GMT

Hi,

The IsNull fonction is not always working properly.

I suggest you to use this instead directly in the laod of your data:

If( len(trim([Field A]))=0,'0',[Field A]) AS [Field A],

Regards

Stéphane

Re: Joins

sanketkhunte — Wed, 18 Dec 2013 08:58:35 GMT

Hi All,

I would like to have word on this.

Such NULL check conditions should handle while pulling the data into Qlik View Data files from DB. I mean while we firing queries on Database. Try to make optimized code @ Qlikview data model layer which give super performance.

Hope this may help.

Thanks

Sanket