topic Re: NULL FIELD PROBLEM IN SET ANALYSIS in App Development

NULL FIELD PROBLEM IN SET ANALYSIS

caio_caminoski — Tue, 28 Nov 2017 09:55:58 GMT

Hello community,

I have a metric that uses a set analysis ( {<FIELD ={'Y'}>} ) . My problem starts when all the values of my 'FIELD' are null. Qlik completely ignores that null is different than 'Y' and don't even bother evaluating the set analysis, giving me the total sum of the value field instead of 0.

For example, in the script:

TABLE:

Load

RowNo() AS CODE

,Null() AS DIM

,1 AS VALUE

AutoGenerate 15;

In the KPI:

Sum( {< DIM = {'Y'} >} VALUE)

As there is no data 'Y' in the field DIM i should be getting 0, but instead I am getting 15.

Any ideas?

Re: NULL FIELD PROBLEM IN SET ANALYSIS

kaushiknsolanki — Tue, 28 Nov 2017 10:19:22 GMT

Hi,

Add two variable as shown below.

NullAsValue *;

Set NullValue = 'NULL';

TABLE:

Load

RowNo() AS CODE

,Null() AS DIM

,1 AS VALUE

AutoGenerate 15;

Now it wont show 15.

Regards,

Kaushik Solanki

Re: NULL FIELD PROBLEM IN SET ANALYSIS

caio_caminoski — Tue, 28 Nov 2017 11:14:28 GMT

Hi Kaushik,

Thank you for your answer. However, we really didn't want to transform null into values. Do you think there is another way to get the set analysis to work with the null values?

This behavior looks like a bug, right?

Re: NULL FIELD PROBLEM IN SET ANALYSIS

kaushiknsolanki — Tue, 28 Nov 2017 11:36:49 GMT

May be it seems as bug.

As a work around try this.

Sum( {<CODE -= {"=isnull(DIM)"},DIM = {"Y"} >} VALUE)

Regards,

Kaushik Solanki

Re: NULL FIELD PROBLEM IN SET ANALYSIS

mikofme — Tue, 28 Nov 2017 11:41:29 GMT

Hi Caio,

it's not a bug. Keep in mind that using SetAnalysis is about the same a selecting values in a listbox. In your LB of field DIM there is no value 'Y' avaliable, so clicking on 'Y' by SetAnalysis just doesn't have any effect - the same as in the real LB.

Add some dummy record to your table, like this:

TABLE:

LOAD * INLINE [

CODE,DIM,VALUE

,Y,

];

and the SetAnalysis statement will be able to select it.

Re: NULL FIELD PROBLEM IN SET ANALYSIS

caio_caminoski — Tue, 28 Nov 2017 16:28:42 GMT

That is a good one! Thank you Kaushik.

At the end I saw that your solution works for the exemple I gave, but in my real table I don't have a unique identifier that I can use as you did in the function.

So I am back to where we were.

Anyway thank you very much again.

Caio

Re: NULL FIELD PROBLEM IN SET ANALYSIS

caio_caminoski — Tue, 28 Nov 2017 17:08:44 GMT

Hi Michael, thank you for your answer,

The idea you are suggesting we also took into consideration, however our data model is complex. We don't want to be concatenating rows or adding inlines "just in case".

I understand what you mean, but I see set analysis as "forced pre-selected filters”, so if I force a field to evaluate my sum only for DIM = ‘Y’ and there is no ‘Y’, my value has to be 0, right?

This is a normal behaviour for a set analysis when there is at least one register not null. If I have a field with ‘Apples’ and ‘Bananas’ and I use in my set analysis ‘Oranges’ the result is zero because there is no match for my pre-set filter.

But thanks again for the suggestion.

Regards,

Re: NULL FIELD PROBLEM IN SET ANALYSIS

kaushiknsolanki — Wed, 29 Nov 2017 06:13:39 GMT

Hi,

Can you tell us what u have in your real scenario.

Regards,

Kaushik Solanki

Re: NULL FIELD PROBLEM IN SET ANALYSIS

caio_caminoski — Wed, 29 Nov 2017 12:22:31 GMT

Hi Kaushik,

Sure! Basically we have a lot of data from different countries. As we have millions of rows, we don't have a field for unique identifiers (such as RowIDs) as the size of the file grows too much.

Then we have a KPI with an expression as it follows:

Sum( {< _Flag = {'Y'} >} Value ) / Sum( {< _Flag = >} Value)

The problem started when we published the app in one stream and restricted the data only to a specific country. By chance it happened that for that specific country all the registers in the field ‘_Flag’ were null. Once it is null, Qlik did not evaluate the set analysis and it is giving 100% instead of 0%.

I am not sure if I was clear. Let me know if it makes sense!

All the best,

Caio

Re: NULL FIELD PROBLEM IN SET ANALYSIS

kaushiknsolanki — Wed, 29 Nov 2017 14:33:00 GMT

It does make sense. Let me try couple of codes and come back to you.

Regards,

Kaushik Solanki

Re: NULL FIELD PROBLEM IN SET ANALYSIS

simonaubert — Wed, 20 Nov 2019 16:42:55 GMT

Thanks.

It totally sounds like a bug or at least a very bad user experience. I lost one hour trying to understand why my set analysis is not working and now. 😞

So, we all agree the {'*'} does not work when the field is totally null. Great. Really great.

Re: NULL FIELD PROBLEM IN SET ANALYSIS

simonaubert — Wed, 20 Nov 2019 16:43:33 GMT

I posted this idea if you want to vote :
https://community.qlik.com/t5/Qlik-Sense-Enterprise-Ideas/Natural-management-of-completly-null-field-in-set-analysis/idi-p/1649280#M838

Re: NULL FIELD PROBLEM IN SET ANALYSIS

jasasri_90 — Tue, 27 Oct 2020 07:51:30 GMT

Hi caio,

Did you find any solution .

Regards,

Jasasri

Re: NULL FIELD PROBLEM IN SET ANALYSIS

simonaubert — Tue, 27 Oct 2020 08:23:06 GMT

Hello,

This is a bug knows as QB-726. I made it open in december 2019, we're in October 2020.

Best regards,

Simon

Re: NULL FIELD PROBLEM IN SET ANALYSIS

phreesia_qv3 — Tue, 13 Apr 2021 18:28:02 GMT

Hello, is there any update to this issue? We are running into the same problem