I need to create a sequence based on the data in a column, the sample table is as follows:

The sequence to be provided is like, if group = bfd then new_id = id + sequence in <0-200> if group = klm or kln then new_id = id + sequence in <201-499>

The output should be something like:

I tried using 2 Sequence generators, first starts from 0 and increment by 1. second starts from 200 and increment by 1, Then using IIF in expression transformation seq_1 = attach NEXTVAL from sequence generator 1 seq_2 = attach NEXTVAL from sequence generator 2

new_id = TO_INTEGER ( IIF( group ='bfd', id || seq_1, IIF( group = 'klm' or group ='kln', id || seq_2) )
)

But the above logic gives output as follows..

The sequence needs to be generated only for the mentioned group..

Is there a way I could achieve this in informatica.. using a UDF or modifying any of my existing logic

Thanks for the help!

1 Answer

This is normal behavior of seq gen. You need to use sorter and expression transformation to do this.

1. Use sorter to sort data on group.
2. Use expression transformation. Create ports like below. io = input/output port, v - variable port, o- output only port.

io_id=id
io_group=group
v_prev_seq1= IIF( io_group ='bfd', IIF(isnull (v_seq1),-1, v_seq1))
v_prev_seq2= IIF( io_group ='klm' or group ='kln', IIF(isnull (v_seq2),199, v_seq2))
v_seq1= IIF(NOT ISNULL(v_prev_seq1),v_prev_seq1+1,0)
v_seq2=IIF(NOT ISNULL(v_prev_seq2),v_prev_seq2+1,200)
o_new_id =
IIF( io_group ='bfd', id || v_seq1, IIF( group = 'klm' or group ='kln', id || v_seq2)) 

Link io_id,io_group,o_new_id to your target.
Explanation -
v_prev_seq1 - in case of bfd, this will be -1 in the beginning. Then when, first row comes, v_seq1 value will be 0. Then for next bfd, it will increase by 1. for non bfd groups this will not increase or be null.
Similarly v_prev_seq2 and v_seq2 will increase only if group is kln or klm.

Pls note, your expected output has an underscore _ in new_id column but in your code its absent. I ignored this as well - pls add if needed.

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.