Count Based on the sequence generated from the data in incremental fashion

Eight Stars

Count Based on the sequence generated from the data in incremental fashion

Hi ,

    Sequence Number                        count

1. seq97016456712017-05-11             1

2 .seq97016456712017-05-11             1

3. seq97016456712017-05-12             2

4. seq97016456712017-05-12             2

How to generate the sequence based on the sequence Number if the sequence number is same the count should remain the same .How to do that ?

 

Twelve Stars

Re: Count Based on the sequence generated from the data in incremental fashion

use tMap.

add 2 var Var1 and Var2.

fill seqnumber into the second var (Var2)

fill the first Var1 with if Var2 != seqnumber then Add 1 else add 0

Something like: (Var.var2==rowX.seqNumber)?Var.var1:Var.var1+1)

Done

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Nine Stars

Re: Count Based on the sequence generated from the data in incremental fashion

One way of achieving this would be using a combination of tSortRow -- tMap. In tMap we basically have to store the previous row value and compare it against the current row value and derive the count.

Sample Job Flow - 

tMap-Seq1.JPG

Sample data -

tFixedFlowInput-1.JPG

 

tMap logic - 

tMap-Seq2.JPG

 

Community Manager

Re: Count Based on the sequence generated from the data in incremental fashion

There is an easy way of doing this with a tMap, using your sequence number. Your sequence is a String and therefore you can use it as the sequence name for the routines.Numeric.sequence(seqName, startValue, step) routine method. So in your tMap you would create a tMap variable with this code in it....

 

routines.Numeric.sequence(row1.seqNum, 0, 1)

I've assumed your sequence number is in a row called "row1" and a column called "seqNum". 

 

Nine Stars

Re: Count Based on the sequence generated from the data in incremental fashion

Hi @rhall_2_0 - I am just trying to understand your suggested method. If I am not wrong by putting a Numeric.sequence on SequenceNumber would result an incremented number being generated for every record and it would start from zero again for any new SequenceNumber. 

Community Manager

Re: Count Based on the sequence generated from the data in incremental fashion

Sorry, I think I may have misunderstood your requirement. What you describe is exactly what would happen, but I see you actually want to group by the sequence number and not increment per sequence number

Eight Stars

Re: Count Based on the sequence generated from the data in incremental fashion

Hi @rhall_2_0 ,

 

I have Done the same using a user routine for getting the distinct count based on some field and i have completed that.

Thanks ,

kasthuri Rengan 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog