topic Re: Set analysis filtering Issue ? in QlikView

Set analysis filtering Issue ?

veeranj — Mon, 07 May 2018 07:08:13 GMT

Hi All ,

I have data as below :

load cust,if(len(trim(desc))=0,null(),desc) as desc;

LOAD * INLINE [

cust,desc

c1,

];

I have to count the below expression :

Count({<desc={'pureatgrp'}>}cust) -- it is giving output as 2, but there are no desc having desc='pureatgrp' .

Not sure of why it is behaving like this .

Thanks,

Veera

Re: Set analysis filtering Issue ?

tresB — Mon, 07 May 2018 07:26:19 GMT

I am not sure, if this is by-design or a bug (I tried with qv version 12.1). If you put at least a single value to the field which is not-null, the set analysis works as expected. If you count the desc field itself -

Count({<desc={'pureatgrp'}>}desc)

with same filter criteria, it works. I could not explain the behavior. Wait and see if for some experts opinion or finding they have.

I have a feeling that this has got to do something with NULL handling and Symbol Table in Qlik (specially when the field is entirely null, i.e. - it doesn't have even a single non-null value).

Re: Set analysis filtering Issue ?

jaumecf23 — Mon, 07 May 2018 07:26:35 GMT

You're right that is not working. If you create an extra field is working correctly. Try this:

load cust,

if(len(trim(desc))=0,null(),desc) as desc,

if(len(trim(desc))=0,0,1) as flag_desc;

LOAD * INLINE [

cust,desc

c1,

];

=Count({<desc={'pureatgrp'},flag_desc={1}>}cust)

Re: Set analysis filtering Issue ?

Peter_Cammaert — Mon, 07 May 2018 08:46:04 GMT

Interesting.

After storing the INLINE table in a QVD, it appears that field desc does have a (xml) definition but no symbol table whatsoever. It simply doesn't have any values (which is exactly what NULL means = no value)

I guess you're right: no symbol table = no values to compare to = set analysis cannot make (temporary) selections = no working set analysis for this field.

Still guessing though. Maybe Oleg can put the record straight.

Re: Set analysis filtering Issue ?

marcus_sommer — Mon, 07 May 2018 12:17:43 GMT

If I remember correctly I had seen a comment from Henric to this topic (I could not find it yet) which was quite the same as your guessing but I'm not quite sure if he considered it as bug (I believe he was).

A solution for such cases could be to avoid a real NULL, for example if the preceeding load in the above example is left than the fieldvalues of desc would be treated as empty strings and not as NULL and the set analysis condition will work like expected (by loading the data from a database the fieldvalues would be really NULL and a transformation with any expression or maybe the NULL variables would be necessary to set them to an empty string or maybe any other string).

- Marcus

Re: Set analysis filtering Issue ?

Oleg_Troyansky — Mon, 07 May 2018 14:46:31 GMT

Yes, this is exactly as many people described here - if the field doesn't have any values, then the Set Analysis filter doesn't work - there is no array of values to attach the Set Analysis results to (1/0). Adding any value anywhere in the same field solves the problem.

Whether it's a bug or just an unfortunate side effect of having a field with no values, I can't say for sure. Thankfully, this is not a very common scenario.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Philly!

Re: Set analysis filtering Issue ?

veeranj — Tue, 08 May 2018 03:58:58 GMT

Hi Marcus and All ,

Thanks for your inputs .

So i tried this way like

load ' ' as Cust,'pureatgrp' as desc autogenerate 1;

and concatenated to the actual table .

Atleast one value should be present in the field on which we are filtering .

Or else

NullasValue desc ;

Set nullvalue='null ;

Both the scenarios are working .

Thanks,

Veera