Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bc-thebruuu
Creator
Creator

Set analysis : Elements list vs Search strings

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?

1 Solution

Accepted Solutions
sunny_talwar

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)


Capture.PNG

View solution in original post

10 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

sunny_talwar

May be this

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

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

An alternate solution could be like:

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

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)


Capture.PNG

Colin-Albert

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.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Extremely useful feedback. Thanks.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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...

sunny_talwar

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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