Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: nisimgarame on 17 Nov 2020 04:29:45 AM

Title: Create table from static values to show 'contains' in data item
Post by: nisimgarame on 17 Nov 2020 04:29:45 AM
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!
Title: Re: Create table from static values to show 'contains' in data item
Post by: nisimgarame on 23 Dec 2020 02:57:58 AM
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.

Title: Re: Create table from static values to show 'contains' in data item
Post by: oscarca on 23 Dec 2020 03:35:59 AM
Hi,

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

best regards,
Oscar
Title: Re: Create table from static values to show 'contains' in data item
Post by: dougp on 23 Dec 2020 03:03:38 PM
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?
Title: Re: Create table from static values to show 'contains' in data item
Post by: nisimgarame on 24 Dec 2020 01:18:42 AM
Thank you for your reply!

Yes, exactly what I meant.
where's the (Toy, Pet, Home) present a long structured list of values.
Title: Re: Create table from static values to show 'contains' in data item
Post by: dougp on 24 Dec 2020 10:33:23 AM
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
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%'

Title: Re: Create table from static values to show 'contains' in data item
Post by: nisimgarame on 27 Dec 2020 07:17:52 AM
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  :(
Title: Re: Create table from static values to show 'contains' in data item
Post by: dougp on 28 Dec 2020 11:03:48 AM
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
Text only | Text with Images