topic Re: Help with set analysis in QlikView

Help with set analysis

saadaouianouar — Sat, 16 Nov 2024 21:43:29 GMT

Hello guys,

I need your help! I have this SQL query and I need to reform it in set analysis!
this is the SQL query :

select IDOSS,count(IDOSS) from
(select distinct IDOSS , IDUnitOrg from UniOrg )
group by IDOSS
Having count(IDOSS) > 1
order by count(IDOSS)

this is what I tried to do :

sum (Aggr (Count(
distinct [__Organizational_Unit_Code] & [__FOLDER_ID]) ,__FOLDER_ID,__Organizational_Unit_Code))

but It shows wrong results because I couldn't translate "Having count(IDOSS) > 1"

Thank you for your help ! I really appreciate it

Re: Help with set analysis

Anil_Babu_Samineni — Mon, 17 Dec 2018 09:57:54 GMT

This is sufficient

Sum(Aggr(count({<IDOSS = {"=Count(IDOSS)>1"}>} IDOSS), IDOSS))

If you have QV 12 above use

Sum(Aggr(count({<IDOSS = {"=Count(IDOSS)>1"}>} IDOSS), (IDOSS,(=Count({1} IDOSS),Desc))))

Re: Help with set analysis

saadaouianouar — Mon, 17 Dec 2018 10:30:36 GMT

Thank you for your help but unfortunally it does not show the wished results! I think we need to include distinct because I'm counting the occurence of IDOSS compared to the IDUnitOrg when count(IDOSS) > 1 .

the set analysis I wrote it shows the results that are > 1 and also the ones that are equal 1.

Thank you for your time 🙂

Re: Help with set analysis

sunny_talwar — Mon, 17 Dec 2018 12:22:56 GMT

Try this may be

Sum({<UniOrg = {"=Count(DISTINCT IDOSS) > 1"}>}Aggr(Count(DISTINCT IDOSS), IDOSS, UniOrg))

or may be give this a shot as well

Count(DISTINCT{<UniOrg = {"=Count(DISTINCT IDOSS) > 1"}>} IDOSS)

Re: Help with set analysis

saadaouianouar — Mon, 17 Dec 2018 15:04:20 GMT

ty for replie but the output of the request must be

and your request output :

Re: Help with set analysis

sunny_talwar — Mon, 17 Dec 2018 15:13:17 GMT

Can you try this

Count(DISTINCT{<IDOSS = {"=Count(DISTINCT UniOrg) > 1"}>} IDOSS)

Re: Help with set analysis

saadaouianouar — Mon, 17 Dec 2018 15:58:49 GMT

Thank you very much for your help but can you please explain it more to me 🙂

Re: Help with set analysis

sunny_talwar — Mon, 17 Dec 2018 16:01:30 GMT

Not sure what is there to explain... all I am doing is DISTINCT counting IDOSS where Count(DISTINCT UniOrg) is more than 1. So, if for a single IDOSS Count(DISTINCT UniOrg) = 1, then it isn't counted.

Does that help?

Re: Help with set analysis

saadaouianouar — Mon, 17 Dec 2018 16:47:36 GMT

Thank you very much Sunny You made my day 🙂

Re: Help with set analysis

sunny_talwar — Mon, 17 Dec 2018 16:48:38 GMT

Awesome 🙂 I am glad I did