Count based on date column

skumar · Post by **skumar** » Thu Sep 10, 2009 3:14 am

Hi All

I have EMPNO,DATE,COUNT columns in my source file and for me the problem is I want to increase my count based on the occurance of the transaction in a particular week for any day.

100 07/09/2009 1
101 07/09/2009 1
102 07/09/2009 1
103 07/09/2009 1

If the existing record comes in any day next week I want the count as below.

100 07/09/2009 1
100 16/09/2009 2
101 07/09/2009 1
102 07/09/2009 1
103 07/09/2009 1

Can some one please throw a light on this?

Thanks in advance.

Regards
Skumar

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Sep 10, 2009 4:43 am

What have you tried so far?

Use sort with change flag.

Pass the result to a transformer with reinitialising a counter on the change flag.

dxk9 · Post by **dxk9** » Thu Sep 10, 2009 4:46 am

Is your source file a static one? Are you loading the details to a destination table?

Can you explain you process and requirement with eg., so that it will be more clear.

Regards,
Divya

srinivas.g · Post by **srinivas.g** » Thu Sep 10, 2009 5:02 am

source file---> sort stage(empno) and create clustered key column is true
--->transformer (check emp no and clustered key column is zero i.e c=c+1)--->target file

dxk9 · Post by **dxk9** » Thu Sep 10, 2009 5:09 am

What will the clustered key column do? I havn't used that before.

Regards,
Divya

chulett · Post by **chulett** » Thu Sep 10, 2009 5:41 am

It's documented and the name is closer to Key Change Column rather than cluster anything, from what I recall. It will contain a 1 for the first record in the 'key group' or a 0 otherwise.