Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

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 

1 Solution

Accepted Solutions

Re: Help with set analysis

Can you try this

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

Re: Help with set analysis

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)
Partner
Partner

Re: Help with set analysis

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

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)
Partner
Partner

Re: Help with set analysis

ty for replie but the output of the request must be   

 cap1.pngcap2.png
and your request output :

cap3.png

Re: Help with set analysis

Can you try this

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

Re: Help with set analysis

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

Re: Help with set analysis

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?

Partner
Partner

Re: Help with set analysis

Thank you very much Sunny  You made my day  🙂

Re: Help with set analysis

Awesome 🙂 I am glad I did