Automatic rownumber with reset on field

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

danielbolt
Posts: 2
Joined: Wed Feb 18, 2015 12:05 pm

Automatic rownumber with reset on field

Postby danielbolt » Wed Nov 18, 2015 8:54 am

Hi,

I'm trying to implement a new column. This column needs to presentate the rownumber, but it needs te be reset when a other field is changing.

ID Name Row
1 Peter 1
2 Peter 2
3 Peter 3
4 Erik 1
5 Erik 2

Check the example above. The rownumber in the column Row is resetting when the value in the field Name is changing.

What i did:
I added a sequence and tried to reset this sequence in a reformat component:

$out.0.Counter = sequence(Activiteit_Telling).reset();

Where do i need to tell the sequence it have to reset on the name field?

Regards,


Daniël

slechtaj
Posts: 192
Joined: Wed Aug 15, 2012 8:18 am

Re: Automatic rownumber with reset on field

Postby slechtaj » Thu Nov 19, 2015 11:40 pm

Hi Daniel,

There are multiple ways to do this.
  • If the your data is sorted you can use a simple CTL transformation that will assign the sequence numbers to records - you do not even need to use Clover sequences as this could be simply handled by simple integer variables.
  • If you don't have the data sorted, you might want to use another approach - Clover lookup tables might help you get this done. In the lookup table, you would just keep the keys with their latest sequences. If you need to keep the sequences between the graph executions, you might want to use persistent lookup tables.
In the attached graph, there are example of both options prepared for your convenience.

StringSeq.grf
(4.88 KiB) Downloaded 215 times
Jan Slechta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com

How to speed up communication with CloverCARE support