Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saadaouianouar
Partner - Contributor II
Partner - Contributor II

Help with set analysis

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 

Labels (6)
1 Solution

Accepted Solutions
sunny_talwar

Can you try this

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

View solution in original post

9 Replies
Anil_Babu_Samineni

This is sufficient

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

Or

If you have QV 12 above use

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
saadaouianouar
Partner - Contributor II
Partner - Contributor II
Author

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 🙂 

sunny_talwar

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)
saadaouianouar
Partner - Contributor II
Partner - Contributor II
Author

ty for replie but the output of the request must be   

 cap1.pngcap2.png
and your request output :

cap3.png

sunny_talwar

Can you try this

Count(DISTINCT{<IDOSS = {"=Count(DISTINCT UniOrg) > 1"}>} IDOSS)
saadaouianouar
Partner - Contributor II
Partner - Contributor II
Author

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

sunny_talwar

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?

saadaouianouar
Partner - Contributor II
Partner - Contributor II
Author

Thank you very much Sunny  You made my day  🙂

sunny_talwar

Awesome 🙂 I am glad I did