Count occasions from many variables

[knivskarp]

Hi!

This is my first post here, so please be gentle :)
I havn´t worked a lot with report studio yet and would need your help on this.

I have variables in a data set (occasions1 - occasions5). The data often only contains not more than one occasion, but when there are text in more than the first variable I don't know how to count them. I want to get a toplist of the occasions, including all the occasion1-occasion5.

An example of the data:

Occasion1   Occasion2    Occasion3    Occasion4    Occasion5
'reasonX'    'reasonY'      'reasonZ'
'reasonZ'
'reasonX'
'reasonZ'
'reasonZ'    'reasonX'     'reasonW'
'reasonZ'
'reasonY'

and so on..

From this small sample I would like to get a toplist of reasons as:

Occasion   Total count
reasonZ    5 times
reasonX    3 times
reasonY    2 times
reasonW   1 time


Is there a simple solution for this problem i´m facing?

Looking forward for some help,

Thanks in advance,

/Knivskarp

[TheFrenchGuy]

Hello,

you can try a "count()" function but I don't know how your data item is linked (time, location, ...)
You can also try a "case when", less automatical, like :

Code Select Expand

case
when (set([Occasion1],[Occasion2] ...) in ('reason X')
then (1)
when (set([Occasion1],[Occasion2] ...) in ('reason Y')
then (1)
else(0)
end

But without any information it's hard to provide a solution =)

[knivskarp]

I´ve used the count and rank functions when getting a toplist for a single Occasion and that works. But i just dont know how to summurize a total count for all 5 Occasion variables :) I´m using relational data with a time variable like YYYY-MM.

[MFGF]

Hi!

This is my first post here, so please be gentle :)
I havn´t worked a lot with report studio yet and would need your help on this.

I have variables in a data set (occasions1 - occasions5). The data often only contains not more than one occasion, but when there are text in more than the first variable I don't know how to count them. I want to get a toplist of the occasions, including all the occasion1-occasion5.

An example of the data:

Occasion1   Occasion2    Occasion3    Occasion4    Occasion5
'reasonX'    'reasonY'      'reasonZ'
'reasonZ'
'reasonX'
'reasonZ'
'reasonZ'    'reasonX'     'reasonW'
'reasonZ'
'reasonY'

and so on..

From this small sample I would like to get a toplist of reasons as:

Occasion   Total count
reasonZ    5 times
reasonX    3 times
reasonY    2 times
reasonW   1 time


Is there a simple solution for this problem i´m facing?

Looking forward for some help,

Thanks in advance,

/Knivskarp

Hi,

So, if I'm understanding, you have five separate query items for your five occasions? I'm not quite sure though, because you refer to "Occasion variables". Can you confirm that they are five separate columns in a table in your database?

If so, I think you need to union them. Add five separate queries - one for each occasion column. Populate each query with a different occasion. Then union all five queries into a single result set query, and base your report off that. You should then be able to count the number of times each reason exists in your one Occasion column.

Cheers!

MF.

[knivskarp]

Yes, that´s correct. 5 different query items, 5 seperate columns.
I´ll try to union them, that´s new for me :)

Thanks for your advice, I´ll come back if i´m getting trouble with the union :)

/Knivskarp

[MFGF]

Yes, that´s correct. 5 different query items, 5 seperate columns.
I´ll try to union them, that´s new for me :)

Thanks for your advice, I´ll come back if i´m getting trouble with the union :)

/Knivskarp

Great! When you are unioning the queries in the Query Explorer you can drag in a Union object from the toolbox and use this to bring all five queries together into a new query.

Good luck!

MF.

[knivskarp]

Hey, this worked!

I got the expected counts when doing this way.

Thank you very much MFGF for your help!

Biggie up! :)