only function qlik sense

[joeybird]

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

[sunny_talwar]

May be this

Concat(DISTINCT {<ID = {"=Temp = 35"}>} ID, ', ')

[diegofcaivano]

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

[sunny_talwar · Accepted solution]

Sample qvf and image attached

[joeybird]

Worked

thank you Sunny

absolutely fab

thank you

[sunny_talwar]

In case you don't want this to change based on selections, you can try this

Concat(DISTINCT {<ID = {"=Only({1}Temp) = 35"}>} ID, ', ')

[mjtaft2017]

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, ', ')

[sunny_talwar]

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?

[mjtaft2017]

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"}>}

[sunny_talwar]

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?