Sequence number generation for same group for sources

[bicap]

Hi All,

I need some help to implement below requirement
Please find my requirement with above sample example.

Iam having two input files "file1" and "file2" comma delimeted file and i need to generate SERIAL column after combining both files.

SOURCE 1
==============

DEPT , EMPNO , SOURCE
sales , s10 , file1
sales , s21 , file1
sales, s31 , file1
plan , p11 , file1
plan , p23 , file1


SOURCE2
===============

DEPT EMPNO SOURCE
sales, s41 , file2
sales , s23 , file2
sales , s32 , file2
plan , p13 , file2
plan , p26 , file2
for

Rule for SERIAL column Generation : Number generation should be started from SOURCE1 starting from 10 and increment by 10 for the records DEPT wise.

For SOURCE2 the SERIAL column should start from the last value from SOURCE 1 for each DEPT wise..


Expected ouput ::

DEPT EMPN SOURCE SERIAL
sales , s10, file1 , 10
sales , s21 , file1 , 20
sales , s31 file1 , 30
plan , p11 , file1 , 10
plan , p23 , file1 , 20
sales , s41 , file2 , 40
sales , s23 , file2 , 50
sales, s32 , file2 , 60
plan , p13 , file2 , 30
plan , p26 , file2 40

Please suggest me how to capture last value for DEPT wise from SOURCE1

[major]

Hi,

Why can't you combine two files and start generating SERIAL after that ?

Thanks
major

[bicap]

We can generate number after combining two files source but , how to generate SERIAL values for SOURCE2 .

Rule for SERIAL column Generation : Number generation should be started from SOURCE1 starting from 10 and increment by 10 for the records DEPT wise.

For SOURCE2 the SERIAL column should start from the last value from SOURCE 1 for each DEPT wise..

[bicap]

Guys please help me on this logic

[priyadarshikunal]

All you have to do is to funnel both sources with one additional column with value 1 for file one and 2 for second one. Sort order should be file number then the other sort keys, partitioned on dept. then use the stage variables to generate the keys, reset when you encounter a different department.