topic How to solve this with SET ANALYSIS in QlikView

How to solve this with SET ANALYSIS

Anonymous — Fri, 02 Oct 2015 23:44:24 GMT

I m trying to use set analysis to count with the following and it is not working, the IF statement is working though.

count({<[Flag Field]-={'1'},

[Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

what is wrong in the above Expn

count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'

and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'

,[Product ID]))

Flag field is a calculated field in the script as below:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt

,1) as "Flag Field"

Re: How to solve this with SET ANALYSIS

sunny_talwar — Sat, 03 Oct 2015 00:05:54 GMT

Try this may be:

=Count({<[Flag Field] = e({<[Flag Field] = {1}>}), [Status] = {'ACTIVE'}, [Field A] = {'Blue'}, [Field B] = e({<[Field B] = {'Plain','Color'}>})>} Distinct [Product ID])

Re: How to solve this with SET ANALYSIS

Anonymous — Sat, 03 Oct 2015 04:31:10 GMT

It is not working Sunny. do you have any other suggestion

Re: How to solve this with SET ANALYSIS

marcus_sommer — Sat, 03 Oct 2015 17:04:38 GMT

I suggest to extend the flag-field to a real else-part like:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt, 1, 0) as "Flag Field"

and then to query them within your set analysis:

count({<[Flag Field] = {0}, [Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

- Marcus

Re: How to solve this with SET ANALYSIS

evan_kurowski — Sat, 03 Oct 2015 18:24:55 GMT

Hello QlikDash,

As Marcus's solution points out, your current [Flag Field] has two possibilities: 1, Null().

The NOT set-analysis operand will not be able to count up the Null() rows of a field.

The following test cannot "grip" the Null area of [Flag Field]:

=Count({<[Flag Field]-={1} >} [Flag Field])

The following can help you measure the existence of Null areas (but not using set-analysis):
=NullCount( [Flag Field])

So per his suggestion, give the areas of missing information a handle (assigning them '0' instead of Null), should give no problem isolating EQUAL and NOT EQUAL areas in set-analysis.

Re: How to solve this with SET ANALYSIS

Anonymous — Sat, 03 Oct 2015 22:56:31 GMT

Marcus and Evan Thank you for the suggestion.

I made the change to include 0 in the script : if(isnull(FieldA) and Main_start_DT < Main_End_Dt, 1, 0) as "Flag Field"

and assign the Flag field to {0} in the set analysis but it is not working

Re: How to solve this with SET ANALYSIS

evan_kurowski — Sun, 04 Oct 2015 00:08:58 GMT

Before you evaluate the user-interface expression, are you certain the script flag is working?

When you check the flag set to 1|0, is it doing so properly according to your targeted logic? The IsNull() function won't pick up empty string or space, because they aren't truly null, and I find that If(Len(Trim(FieldA)) is often a more flexible predictor of missing or empty info.

Also, check your Main_start_DT < Main_End_Dt evaluation, and ensure that is evaluating properly.

Put the [Flag Field] in a list box by itself and check the 'Show Frequency' options. Does the frequency distribution cover areas of both 1 & 0, or is it 100% on just one value. If it 100% on just one value, the script evaluation probably has the issue.

Re: How to solve this with SET ANALYSIS

Digvijay_Singh — Sun, 04 Oct 2015 07:38:17 GMT

While going through your expression I found something which I was not aware of - the case-in-sensitivity of set analysis, I used your expression and started new discussion to understand it better - Re: Set Analysis - is it case insensitive ?

Your expression worked fine with the sample data I used so I think you may need to see your data further to understand why it is not working -

Just attaching the sample I have used, may be it can be of any help.

Thanks,

Re: How to solve this with SET ANALYSIS

marcus_sommer — Sun, 04 Oct 2015 09:07:17 GMT

I believe like Digvijay that there are further reasons why it's not worked for you at the moment and think you should look at his answer.

- Marcus

Re: How to solve this with SET ANALYSIS

evan_kurowski — Sun, 04 Oct 2015 16:23:33 GMT

I m trying to use set analysis to count with the following and it is not working, the IF statement is working though.

count({<[Flag Field]-={'1'},

[Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

what is wrong in the above Expn

count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'

and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'

,[Product ID]))

Flag field is a calculated field in the script as below:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt

,1) as "Flag Field"

While I agree bugs related to inconsistency of set-analysis/search case-sensitivity is a major concern (please fix this asap, because I ❤️ set-analysis), I'm not certain it is the only possibility here.

You can see in the requested set-modifiers, there is variance in attempts to match by case. The [Status] modifier sought is upper, while [Field A], [Field B] requests are capitalized.

[Status]={'ACTIVE'}, //attempt to match a fully uppercase value
[Field A]={'Blue'}, //attemp to match capitalized value
[Field B]-={'Plain','Color'} //attempt to match capitalized value

This implies two possibilities:

The variance in case in set-modifiers was inadvertent and QlikDash was not paying attention to case when forming set-analysis. (A big Qlik no-no, as Qlik developers are made aware at nearly all stages that case sensitivity matters)
The variance in case of requested set-modifiers was deliberate and QlikDash was specifically trying to match case-sensitive values.

In which case, the set-analysis case-sensitivity bug would only come into play if:

- the actual contents of a set-modifier are a case insensitive match within the search field
- are mutually exclusive in terms of a case-sensitive match.

(i.e. the expression would only be bug impacted under the following conditions:

[Status] Set-modifier = ‘ACTIVE’ | field only contains: ‘active’,’Active’

[Field A] Set-modifier = ‘Blue’ | field only contains: ‘BLUE’,’blue’

[Field B] Set-modifier = ‘Plain’,’Color’ | field only contains: ‘PLAIN’,’plain’,’COLOR’,’color’

I'm not listing every variant of case possibility like plAiN, coLOr, etc.. but you get the idea)

However…

QlikDash goes on to assert that the following IF statement is working:
=count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'
and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'
,[Product ID]))

As far as we are aware, IF statements do not have an issue matching on case-sensitivity. Which implies the following field values are present and being properly detected in the data:

[Status]: ‘ACTIVE’

[Field A]: ‘Blue’

[Field B]: ‘Plain’, ‘Color’

Based on the working IF statement providing “proof of field contents”, if these field values are indeed present, then the set-modifiers from the set-analysis version should still be able to produce correct results, regardless of the bug (non-wildcard equi/NOT equi matches are not broken when their exact case-sensitive matches are present in the data).

Re: How to solve this with SET ANALYSIS

Anonymous — Mon, 05 Oct 2015 21:50:49 GMT

Hi all,

Thank you so much for providing valuable feed back. The discussion took a different direction, however, probably for a much better insight and discovery surrounding set analysis. The goal behind my question was to find why set analysis was not "gripping" non-1 values. It was not really about case sensitivity. but my good luck, that issue was also identified and discussed with a result.

As it turns out, I was not able to count [Flag Field] with non-1 values because in my data model, I had a where clause which was loading records where [Flag Field] =1; i disabled that and set analysis correctly picked/"GRIPPED" as Evan_Kurowski‌ likes to call it.

one thing is clear though: If statement picks up non-1 values even when Flag Field is not defined with (1,0) whereas Set analysis seems to only work when Flag Field is articulately defined with a (1,0).

so I will pick Marcus suggestion because he pointed out first, the possibility of not gripping due to absence of (1,0) in the calculated field.

Re: How to solve this with SET ANALYSIS

Anonymous — Mon, 05 Oct 2015 21:57:03 GMT

I have attached the qvw that has where clause restricting "0" values of Flag Field.

Thank you sunindia‌ MichelSommer‌ Digvijay‌ Evan_Kurowski‌

Please feel free to correct my understanding. I am dependent on qlik community for what I do to a good extent.

Thanks again