Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Why?
And How can I get 4 using a search string?
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)
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
May be this
While my explanation would be inline with Peter's above.
An alternate solution could be like:
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)
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.
Extremely useful feedback. Thanks.
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...
Oh nice... Happy Birthday Mrs. Cammaert ... It will be easy to remember your wife's birthday as tomorrow is my wedding anniversary
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