topic Re: Set analysis : Elements list vs Search strings in QlikView

Set analysis : Elements list vs Search strings

bc-thebruuu — Wed, 25 Nov 2020 16:16:04 GMT

Hello

I thought to be pretty good at Set Analysis but recently I bumped in a weird case:

I need to analyse the temporal evolution of some data and ended up to use search strings in my set analysis.

Using this solution,

I have been faced with weird result that I have been able to reproduce in a simple use case:

Let's load those data:

LOAD * INLINE [

KEY, VALUE

A, 1

A, 2

B, 1

C, 2

C, 1

D,1

D,3

];

count({<VALUE={'1'}>}KEY) is equal to 4 ... not surprinsingly

BUT

count({<KEY={"=(VALUE=1)"}>}KEY) IS = 1 ????? It seems to count only Those Key whose only value is 1!?

Why?

And How can I get 4 using a search string?

Re: Set analysis : Elements list vs Search strings

Peter_Cammaert — Fri, 16 Feb 2018 10:38:02 GMT

Just a possible explanation:

Apparently, the search string solution looks for Keys that only have a VALUE=1. For the others, there are multiple solutions per Key and your VALUE=1 expression won't work because of those multiple values. It probably compares {1, 2} = 1

Re: Set analysis : Elements list vs Search strings

sunny_talwar — Fri, 16 Feb 2018 10:39:49 GMT

May be this

Count({<KEY={"=SubStringCount(Concat(DISTINCT '|' & VALUE & '|'), '|1|') = 1"}>}KEY)

Re: Set analysis : Elements list vs Search strings

tresB — Fri, 16 Feb 2018 10:42:40 GMT

While my explanation would be inline with Peter's above.

An alternate solution could be like:

count({<KEY={"=Index(Concat(Distinct VALUE, '@'),'1')"}>}KEY)

Re: Set analysis : Elements list vs Search strings

sunny_talwar — Fri, 16 Feb 2018 10:54:51 GMT

Actually, you would need to do a DISTINCT count of KEY in this case

Count(DISTINCT {<KEY={"=SubStringCount(Concat(DISTINCT '|' & VALUE & '|'), '|1|') = 1"}>}KEY)

UPDATE: This is another possibility

Count(DISTINCT {<KEY = P({<VALUE = {1}>})>}KEY)

Re: Set analysis : Elements list vs Search strings

Colin-Albert — Fri, 16 Feb 2018 11:00:39 GMT

The reason you are getting the result you mention is, I believe, because the VALUE column is referenced as a "naked" dimension with no aggregation expression. Any naked value will be interpreted using only() as the aggregation expression within Qlik.

So VALUE will be interpreted by the calculation engine as ONLY(VALUE) which returns a value when there is a single matching value, if there are multiple values that match then null is returned.

Sunny stalwar1‌ has given a solution to get around this problem.

Thanks to hic for his presentation at yesterday's Qlik Dev Group Guru Day which covered this issue.

Re: Set analysis : Elements list vs Search strings

Peter_Cammaert — Fri, 16 Feb 2018 11:10:11 GMT

Extremely useful feedback. Thanks.

Re: Set analysis : Elements list vs Search strings

Peter_Cammaert — Fri, 16 Feb 2018 11:12:56 GMT

Makes me feel the pain of not being able to be present yesterday (all those heroes in one spot !). But then, it was the wife's birthday...

Re: Set analysis : Elements list vs Search strings

sunny_talwar — Fri, 16 Feb 2018 11:15:08 GMT

Oh nice... Happy Birthday Mrs. Cammaert ... It will be easy to remember your wife's birthday as tomorrow is my wedding anniversary

Re: Set analysis : Elements list vs Search strings

Peter_Cammaert — Fri, 16 Feb 2018 11:21:08 GMT

Oh, so you are in a similar situation as I am. What did you do, merge Valentine's day into your wedding anniversary?

I tried to do that but it didn't work out

Re: Set analysis : Elements list vs Search strings

bc-thebruuu — Fri, 16 Feb 2018 12:24:22 GMT

Thank you very much