Seven 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 ?

 

6 REPLIES
Nine 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

Don't forget to tag when it's "solved"!

Six 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

 

Fifteen Stars

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". 

 

Rilhia Solutions
Six 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. 

Fifteen Stars

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

Rilhia Solutions
Seven 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