Mapping multiple rows to a single row
Posted: Wed Feb 22, 2006 3:52 am
Hi all,
Here's the requirement that i'm facing:
Can a Datastage process be designed for doing the following:
Input file
CHAIN CDE1 CDE2 CDE3 CDE4 AMT CNT
100, DM, N, N, BT, 300, 2
100, DM, N, N, AC, 200, 1
100, EM, N, N, BT, 100, 3
200, DM, N, N, BT, 300, 2
200, DM, N, N, AC, 200, 3
200, EM, N, N, BT, 400, 5
Business Rule:
Based on the data in the CDE1,CDE2,CDE3,CDE4 the AMT will go to the correponding fields in the output for that CHAIN, so for Chain 100 there will be one record in output and similarly for Chain 200 there will be one record.
Output file
CHAIN DM_AMT_BT DM_AMT_AC EM_AMT_BT DM_CNT_BT DM_CNT_AC EM_CNT_BT
100 300 200 100 2 1 3
200 300 200 400 2 3 5
I tried exploring Combine Records stage but couldn't proceed after a point.
One option that can be used is a transformer with stage variable but it can be effective only if data is directly written/updated in the table. In this scenario, output goes to a dataset.
Please help me out!
Thanks in advance,
Chits
Here's the requirement that i'm facing:
Can a Datastage process be designed for doing the following:
Input file
CHAIN CDE1 CDE2 CDE3 CDE4 AMT CNT
100, DM, N, N, BT, 300, 2
100, DM, N, N, AC, 200, 1
100, EM, N, N, BT, 100, 3
200, DM, N, N, BT, 300, 2
200, DM, N, N, AC, 200, 3
200, EM, N, N, BT, 400, 5
Business Rule:
Based on the data in the CDE1,CDE2,CDE3,CDE4 the AMT will go to the correponding fields in the output for that CHAIN, so for Chain 100 there will be one record in output and similarly for Chain 200 there will be one record.
Output file
CHAIN DM_AMT_BT DM_AMT_AC EM_AMT_BT DM_CNT_BT DM_CNT_AC EM_CNT_BT
100 300 200 100 2 1 3
200 300 200 400 2 3 5
I tried exploring Combine Records stage but couldn't proceed after a point.
One option that can be used is a transformer with stage variable but it can be effective only if data is directly written/updated in the table. In this scenario, output goes to a dataset.
Please help me out!
Thanks in advance,
Chits