Problem with the differences in values when loading null values from XML and by joining

[#1]

Hi!

I'm having problems with making expressions when I have null and missing values in my data model. I have initially loaded data from XML files into QlikView and when I load rows which have values from certain columns missing, QlikView leaves the columns empty as if the values are missing.

I have had to join two tables together for data model optimization and when I have joined certain rows some of which have values in the other table and some which do not. The rows which I have joined and which did not have any values in the right table have been marked as null values ( - ) rather than empty/missing.  You can see a representation of the data here:

So now I have null and missing values in my data which makes the calculation of percentages hard because even if I check the 'Suppress when value is null', I still have the missing data row which I do not wish to include as I want to calculate percentages for 'hakemus.yleinen_arvio_pisteet' 1-4 values sums of the 'Hakija' column.

I'm wondering if I should fix this in the script or the expression?

I need to filter out the missing values in many places so it would be a benefit to do this on the scripting side. I already tested Set NullInterpret and NullAsValue but I have not been able to combine null and missing values.

On the expression side, I'm currently using the following expression in my chart to display the correct fractions:

=Count(hakemus.yleinen_arvio_pisteet) & '/' & sum(total <lukuvuosi> hakija.Lkm)

I take it this would work if I could filter out the null and missing values somehow.

Also, I'm looking to calculate the changes in the percentages of 'Hakija' values compared to the previous year. What kind of expression could I use for this?

All the best,

Mikael

[#2]

Hello Mikael,

You can check this article:

QlikView Addict: Handling Nulls in QlikView

Thanks,

ASINGH

[Jason_Michaelides]

I would always work in the script to remove all NULL values and replace them with something selectable like 0 or <Unknown>.  In your case you have two types of NULLs (or rather, NULL values create in two ways):

1. NULLs in your source data
2. NULLs created by the lack of right-sided records in a LEFT join.

I have found the easiest and most controllable way to replace NULL values generally is to use MAP...USING, but you first have to understand that NULLs created by the join don't actually exist until the join is completed and the table loaded. Therefore, to replace NULLs created by a join you have to do something to the newly joined table to force it to be read again.  For example:

Map_Nulls_Numeric:

MAPPING LOAD

     Null()

     0

Autogenerate 1;

Map_Nulls_Text:

MAPPING LOAD

     Null()

     '<Unknown>'

Autogenerate 1;

MAP NumericField1,NumericField2,NumericField3 USING Map_Nulls_Numeric;

MAP TextField1,TextField2,TextField3 USING Map_Nulls_Text;

Data:

LOAD * FROM DataSource1;

LEFT JOIN (Data)

LOAD * FROM DataSource2;

RIGHT JOIN (Data) LOAD DISTINCT * RESIDENT Data;

This final line joins the Data table to itself which effectively does nothing except force it to be loaded again. In doing so, the NULLs that were created by the LEFT JOIN are now recognised and replaced with the mapping function.

It adds script execution time, but make performance and usability in the UI much better.

Hope this helps,

Jason