topic Re: only function qlik sense in App Development

only function qlik sense

joeybird — Fri, 31 Mar 2017 14:40:53 GMT

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

Re: only function qlik sense

sunny_talwar — Fri, 31 Mar 2017 14:51:01 GMT

May be this

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

Re: only function qlik sense

diegofcaivano — Fri, 31 Mar 2017 19:44:11 GMT

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

Re: only function qlik sense

sunny_talwar — Sat, 01 Apr 2017 15:36:46 GMT

Sample qvf and image attached

Re: only function qlik sense

joeybird — Mon, 03 Apr 2017 11:01:18 GMT

Worked

thank you Sunny

absolutely fab

thank you

Re: only function qlik sense

sunny_talwar — Mon, 03 Apr 2017 11:02:19 GMT

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

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

Re: only function qlik sense

mjtaft2017 — Mon, 31 Jul 2017 21:54:58 GMT

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

Re: only function qlik sense

sunny_talwar — Tue, 01 Aug 2017 02:12:43 GMT

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?

Re: only function qlik sense

mjtaft2017 — Tue, 01 Aug 2017 14:52:08 GMT

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

Re: only function qlik sense

sunny_talwar — Tue, 01 Aug 2017 15:02:30 GMT

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?

Re: only function qlik sense

mjtaft2017 — Tue, 01 Aug 2017 16:12:02 GMT

Sunny -

thanks for your reply. It is making more sense now. I am playing around with different aspects of this, changing temps and seeing the impact. It just puzzled me that this -> {=Temp=35} was functioning like Only(Temp).

Thanks again

Re: only function qlik sense

sunny_talwar — Tue, 01 Aug 2017 16:13:51 GMT

It was working like this

{=Only(Temp) = 35}