Joins

[#1]

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 )

[#2]

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)

[#3]

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?

[#4]

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.

[rbecher]

HI,

have you tried to use in script:

SET NullValue='0';

NullAsValue a;

- Ralf

[#6]

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.

[#7]

For the Full Outer join it is not working properly.

[#8]

Scramble the data and load a sample qvw.

[#9]

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

[#10]

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