Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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))))

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