Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I only want to count IDS, that have ONLY had temperature observations of 35
i.e
ID Obs Temp
1 1 35
1 2 35
1 3 35
2 1 35
2 2 36
2 3 35
2 4 37
3 1 35
3 2 35
3 3 35
3 4 36
I should only return ID 1
Please help
Sample qvf and image attached
May be this
Concat(DISTINCT {<ID = {"=Temp = 35"}>} ID, ', ')
Hi Joanna,
If you are looking for how many IDs match your condition, you can use this expression:
SUM(IF(AGGR(ONLY(TEMP), ID)=35, 1))
If you need to show which IDs they are, use this other expression in a table dimensioned by ID.
ONLY(TEMP)=35
Regards,
Diego.-
Sample qvf and image attached
Worked
thank you Sunny
absolutely fab
thank you
In case you don't want this to change based on selections, you can try this
Concat(DISTINCT {<ID = {"=Only({1}Temp) = 35"}>} ID, ', ')
I am new to this - so I am trying to follow any "set analysis" posts I can. When I watch the videos on set analysis they all make sense until I try to take a problem and solve it myself. I came up with this
count(distinct {<ID=,Temp={35}>}ID )
which doesn't work because it is not taking into account the result should be IDs that have ONLY had temp=35 and no other temp readings. However I don't quite follow the logic of the correct answer which you posted.
particularly putting concat on the front and then this part {"=Temp = 35"}. and why the ',' at the end? I hope it isn't too much to ask if you can explain it -- thank you very much
Concat(DISTINCT {<ID = {"=Temp = 35"}>} ID, ', ')
Read about concat function here....
Concat - script function ‒ QlikView
It is used to concat multiple values and the comma is separator. We don't see comma here because the output is just a single ID, if there were more than one ID (let's say 1 and 5), you would see something like this 1,5
Does that make sense?
Ok - I understand how the concat part works and why there is not a comma ( in this case only 1 ID meets the criteria). What about this part {"=Temp = 35"} - how does that accomplish pulling the ID's that only have temp of 35 degrees vs IDs that have temp 35 and also other temps which is what I got by this {<ID=,Temp={35}>} (which did not exclude those same IDs which had other temps also). I am missing something. How does that accomplish excluding the IDs which also have OBS other than temp=35. I can see how this would work since it addresses the ONLY requirement
{<ID={"=Only(Temp)=35"}>}
Here is the deal
Temp = {'35'} means that show all rows where Temp = 35.... it doesn't check if two IDs have more than 1 Temp and one of them is not 35
ID = {=Temp = 35} -> This checks if an ID has Temp = 35... If for some reason an ID have more than one Temp, Temp (which is essentially Only(Temp)) will become null... So all those IDs where we have more than 1 Temp are excluded. Next, we have all those IDs where we have only one value for Temp (they can have repeating same value so ID 1 can have two rows of Temp = 35 and this will be included). Within these IDs, pick those IDs where Temp = 35
Does it make sense?