Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bcavestro
Contributor

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?

Tags (1)
1 Solution

Accepted Solutions

Re: Set analysis : Elements list vs Search strings

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

Re: Set analysis : Elements list vs Search strings

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

May be this

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

Re: Set analysis : Elements list vs Search strings

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

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

Re: Set analysis : Elements list vs Search strings

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

Extremely useful feedback. Thanks.

Re: Set analysis : Elements list vs Search strings

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

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

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