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
Generate Id's based on the key field
Moderators: chulett, rschirm, roy
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
Use 3 stage variables in the transformer, sort and partition the data on the MEMBER_ID.
I dont have access to datastage right now, but the above logic should work.
Code: Select all
MEMBER_ID --> svNew
If svOld = svNew then svCnt + 1 else 1 --> svCnt
svNew --> svOld
Thanx and Regards,
ETL User
ETL User
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.cherry wrote:Let not confuse with the sample data above.
As to your "any idea" question I'd already posted a solution.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers