# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Labels (6)

• ### SQL

1 Solution

Accepted Solutions
MVP

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

## 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 🙂

MVP

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

## Re: Help with set analysis

ty for replie but the output of the request must be

MVP

## Re: Help with set analysis

Can you try this

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

## Re: Help with set analysis

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

MVP

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

## Re: Help with set analysis

Thank you very much Sunny  You made my day  🙂

MVP

## Re: Help with set analysis

Awesome 🙂 I am glad I did