Count Distinct

[nonooo]

Hi everybody,

I am new to cognos and I am trying to learn the basics.
So as far as I have searched on the net it seems to be a "popular" request (and problem) and I have managed to get some success with solution I do not actually like.

I explain the problem:
I got three CSV file as datasources, these CSV files are extracted from a relational database. Two of them are linked to the last one with a 1-n relation (--> the PK of one is a FK in two others)
I want to make a distinct count on the PK using dimension in all three tables.

As I previously said, I managed to do this by making the linking using SQL to make a big datasource BUT I would like to achieve this count with the three datasources (I mean without "preparing" the data)

I found on the cognos support site something that seems interesting with calculated colums (value =1 for only one row of the group and =0 for the others, so the distinct count becomes a sum... sorry if this is not clear) but to achieve this I had to know how to had a calculated IDkey column and after that a formula with the "for" keywords (but I do not see it, that makes me wonder if this is an impromptu keyword).

Anyway, could someone indicates me if I am on the good way or if what I am trying to do is impossible...

Many thanks in advance to those who had read this message 'till the end.

[nonooo]

the fact is that I do NOT have impromptu, I just got transformer...

so I do not see where to put your formula: (correct me if I am wrong)
-> this is not possible in calculated measure (as count, distinct and for are not valid function)
-> this is not possible in calculated column (hu that was there I thought I could have done it in the beginning but now I had understood that column must be calculated from only one row of the datasource)

and maybe there are other place but I simply do not have seen them!
---------------------

anyway I think you understand me clearly because this is almost the same thing as in
http://support.cognos.com/supported/supportlink/8/8n1.pdf (page 31)

my BIG problem is not to use a query as a datasource in transformer.

[MFGF]

Hmmm - that copy of SupportLink is 8 years old - probably refers to Transformer 5 or 6.  Transformer has moved on quite a bit since then!.

First, in the Dimension Map, find the level containing the item you want counted, go into the properties of the level, and mark it as being unique.  Then add a new measure to the Measures window, and specify the type as Category Count.  Choose the dimension and level that contains your unique item.

Does this give you what you require?

Regards,

MF.

[nonooo]

Yes that works with a big datasource where the linking between tables are already done.

but with three tables that counts strange things, as far I have understand and analyze it counts the same things but you can not use at the same time dimension from the linked table. I know transformer add measures at the intersection of dimension and so do not actually link the tables and the resulting (problem) is logic as the information I want is not in the cube (I mean what I want is a kind of filter)

I try to be more clear with a schema:
A        - B(with PK) - C
dima1                        dimc1
dima2                        dimc2

so when you choose dima1 it is the same as selecting a set of PK (that obviously are in A and B and "maybe" in C)
the same for dima2
dimc1 and dimc2 does the same (but not on the same table)

ok so now you got a more precise idea of my datasources
I want the distinct count of the intersection of both filters

I know how to get the count (but not distinct) by using calculated column on A anb B and adding measure on this calculated column (but in fact it only counts the number of record affected)

I hope I gave enough explanations to make myself clear.

and again thank you for trying to help

[nonooo]

I know I am replying to myself (but I am not crazy I swear it!)
this is just to help people that got the same problem as me...


So I am not sure of the solution but I am now sure it needs a measure allocated by another measure, I need to do some checks with false simple data to see if it works and If everything works great I will post what I have done to create my measure.

[nonooo]

after searching a lot... it seems it is not possible as the three tables can be considered as unrelated fact tables, so transformer does not know how to put the data in the right category.

I give up!