Create table from static values to show 'contains' in data item

[nisimgarame]

Hello,

I searched a lot for a solution and I hope somebody can help me here...

I have free-text data item (Subject) and order no.

I need to show static list of subject like this:
Toy
Pet
Home


I want to show in a table how many times each subject occures in distinct order no.

For the following data set:

order no.         subject
1212               Hometoy
2245               PetToy
2245               Pet

To show:

Subjet      distinct count
Toy              2
Pet              1
Home          1


I hope I was clear enough.

Thank you in advance!

[nisimgarame]

Please help.

My only solution is to create a query and a data item for each object.
My problem is that there is over 50 objects and I think there should be a better way.

[oscarca]

Hi,

Have you tried: count([Order no.] for [Subject]) or count(distinct [Order no.] for [Subject]) ?

best regards,
Oscar

[dougp]

Let's see if I understand the question.


For the following data set:

order no.         subject
1212               HomeToy
2245               PetToy
2245               Pet
2245               PetToyPetPetToyPetHome
2245               Toy
2246               Home
2247               Home
2248               Home
2249               Home
2250               Home


Show:

Subject      Count of distinct order no
Toy              2
Pet              1
Home          7


Is that correct?

[nisimgarame]

Thank you for your reply!

Yes, exactly what I meant.
where's the (Toy, Pet, Home) present a long structured list of values.

[dougp]

Answer in SQL because Cognos is really hard to type.  I assume since you work with data that you know a data language and since you work with Cognos you can translate that into what to do in Cognos.

Code Select Expand

select 'Toy' as Subject
, count(distinct [order no.]) as OrderCount
from orders
where subject like '%Toy%'

union
select 'Pet' as Subject
, count(distinct [order no.]) as OrderCount
from orders
where subject like '%Pet%'

union
select 'Home' as Subject
, count(distinct [order no.]) as OrderCount
from orders
where subject like '%Home%'

[nisimgarame]

Thank you doug!

I need to verify with you - is that the only way?

I avoided using this mathod, because as I wrote before - I have over 50 subjects... So it's a lot of syntacs to write  :(

[dougp]

Nothing elegant pops into my head.  Consider...
order no.    subject
2245         PetToyPetPetToyPetHome

How would you turn that into 3 records?
Subject      OrderCount
Toy          1
Pet          1
Home         1