Generate Id's based on the key field

[cherry]

Dear All,
I have the following requirement. I have a member who can have multiple benefits as shown below:
MEMEBR_ID NAME BENEFIT
NMBR001 AAA 1
NMBR001 AAA 2
NMBR002 BBB 1
NMBR002 BBB 2
NMBR002 BBB 3

In the above example NMBR001 has got 2 benefits and NMBR002 has got 3 benefits. I wanted the output to be as shown below:
If a member has more than one benefit, I wanted the ENTITY_ID to get generated and increment by 1
MEMEBR_ID NAME BENEFIT ENTITY_ID
NMBR001 AAA 1 ANR001
NMBR001 AAA 2 ANR002
NMBR002 BBB 1 ANR001
NMBR002 BBB 2 ANR002
NMBR002 BBB 3 ANR003

Will someone guide me the ETL design here? Appreciate any help.

Best Regards
Cherry

[chulett]

I'm guessing your example posted is not representative of the actual data, if it was you'd just use the BENEFIT to number the ENTITY_ID. Meaning there'd be no reason to generate or increment anything. However...

Use a Sort stage to get the data into the proper order and have it generate a Key Change Column which is explained in the documentation. Use stage variables in a transformer to set your entity id to its starting value when the key change column is a 1 and then increment it by one when it is a 0.

[cherry]

Hi Chulett,

Let not confuse with the sample data above. To make it simple it is shown as below

INPUT -->

ID_NUMBER

1234
1234
1234
1111
2345
2345


Output required --->

ID_NUMBER | ENTITY_ID

1234 | 001
1234 | 002
1234 | 003
1111 |001
2345 |001
2345 | 002

Any idea.

Regards
Cherry

[chandra.shekhar@tcs.com]

Use 3 stage variables in the transformer, sort and partition the data on the MEMBER_ID.

Code: Select all

MEMBER_ID  --> svNew
If svOld = svNew then svCnt + 1 else 1 --> svCnt
svNew --> svOld

I dont have access to datastage right now, but the above logic should work.

[chulett]

Let not confuse with the sample data above.

Best way to not confuse people? Don't post "simple" or made up sample data, make sure it is representative of the actual data. That way people don't post invalid answers.

As to your "any idea" question I'd already posted a solution.