Count Distinct driving me insane

[Arsenal]

Hi guys,

We use Framework Manager generated IQD's for Transformer.

There's a requirement for the following:

Refund ID     Transmittal Number      Order No.     Refund Amount   

101              1001                           01              -100
101              1002                           02               -50
102              1003                           03                -200
102              1004                           04               -150

So, the requirement is that the item counts for refund ID's should be a 1 for refund amount*-1 >0


The above should return 2

A category count within Transformer is not feasible, since the distinct refund id's expected are about 4-5 million. I also tried a rollup and duplicate rollup manipulation for the refund id column treated as a measure, but that's giving ambigous results

Is there any other way I could achieve the requirement? Maybe within Framework Manager?

[Andrei I]

The question is not quite clear.
Did you mean that you have to count every record with negative  'Refund Amount'?


>So, the requirement is that the item counts for refund ID's should be a 1 for refund amount*-1 >0
>The above should return 2

[Arsenal]

Problem is solved

We decided to create a separate cube off a data source query subject in FM. We are manually doing a Group by on the SQL and inserting a dummy value of 1 per record, so we are now getting distinct counts for Refund ID.

Thanks

[cognos_users]

Hi Arsenal,

I am facing the same issue. We need distinct count an a field with around 200,000 distinct values. Please elaborate your approach as how you were able to resolve it.

Thanks!