Distinct Count of Dates

[AnthonyMJ]

I have table as below and would like to create a column that displays the distinct count of all the date columns and reset on change of Customer and Product

Customer Product   Date1      Date2   
--------+-------+----------+----------+
ABC      123      01/01/08   02/15/08 
ABC      123      02/01/08   02/17/08
ABC      123      01/01/08   02/18/08
ABC      123      01/31/08   02/15/08
ABC      456      02/01/08   03/07/08
ABC      456      02/10/08   03/09/08
ABC      456      02/05/08   03/07/08
ABC      456      02/01/08   03/10/08

Desired Result Set

Customer Product   Date1    Ctr1    Date2   Ctr2
--------+-------+----------+----+---------+-------+
ABC      123      01/01/08   1    02/15/08   1
ABC      123      02/01/08   2    02/17/08   2
ABC      123      01/01/08   1    02/18/08   3
ABC      123      01/31/08   3    02/15/08   1
ABC      456      02/01/08   1    03/07/08   1
ABC      456      02/10/08   2    03/09/08   2
ABC      456      02/05/08   3    03/07/08   1
ABC      456      02/01/08   1    03/10/08   3

In the above result set, I have added Ctr1 to get a distinct count of Date1 and Ctr2 for Date2.

Appreciate any assistance.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

Insert a running total that does a distinctcount of date and resets on change of group: product. It will display like this though:

Customer Product Date1 Ctr1 Date2 Ctr2
--------+-------+----------+----+---------+-------+
ABC 123 01/01/08 1 02/15/08 1
ABC 123 02/01/08 2 02/17/08 2
ABC 123 01/01/08 2 02/18/08 3
ABC 123 01/31/08 3 02/15/08 3
ABC 456 02/01/08 1 03/07/08 1
ABC 456 02/10/08 2 03/09/08 2
ABC 456 02/05/08 3 03/07/08 2
ABC 456 02/01/08 3 03/10/08 3

If you grouped on customer and on product and placed the running total in the product group footer, you would see the correct counts per customer/product combination.

-LB

 

[AnthonyMJ]

Thank you for the response.

I have tried using running total but as you said, the result is somewhat different.

The distinct count is actually going to be used to store the dates in an array. This array will be used to plot the dates on a manual gantt chart represented by icons (Size and Position option to be used here). If I used the running total as counter for Date1, I will end up with the below

Product  sv_Date1 (Array)
--------+-------------------
123      Date1[1]=01/01/08
123      Date1[2]=01/01/08
123      Date1[3]=01/31/08
456      Date1[1]=02/01/08
456      Date1[2]=02/10/08
456      Date1[3]=02/01/08

Desired Result is

Product  sv_Date1 (Array)
--------+-------------------
123      Date1[1]=01/01/08
123      Date1[2]=02/01/08
123      Date1[3]=01/31/08
456      Date1[1]=02/01/08
456      Date1[2]=02/10/08
456      Date1[3]=02/05/08

Or if we can somehow sort the dates internally in sequence so that the array gets the dates in order is even better (ie. Date1[1]=01/01/08, Date1[2]=01/31/08, Date1[3]=02/01/08 and so on)

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[lbass]

You should have said that up front. But I don't really see what you mean about using a distinctcount to store a date in an array. If you just want distinct values in the array, you can use something like:

datevar array x;
numbervar i := i + 1;
if not({table.date} in x) then
x := {table.date};

This wouldn't get them in order, but sorting would.

-LB