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
Mapping multiple rows to a single row
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Mon Jul 18, 2005 4:07 am
-
- Participant
- Posts: 60
- Joined: Sun Nov 21, 2004 2:24 am
Re: Mapping multiple rows to a single row
Can you elaborate more as how output is getting derived from input.
chitrangadsingh wrote: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
-
- Participant
- Posts: 12
- Joined: Mon Jul 18, 2005 4:07 am
Output is direct mapping of AMT and CNT fields to the respective fields in o/p depending upon the values in CDE1...CDE4 cols. There r no calculations involved.
Lets say, if CDE1='DM' and CDE4='BT' then AMT will map to a field called DM_AMT_BT...and so on.Similarly for CNT field.
Hope this clears the doubt.
Lets say, if CDE1='DM' and CDE4='BT' then AMT will map to a field called DM_AMT_BT...and so on.Similarly for CNT field.
Hope this clears the doubt.
Hi,
I dont find any dificulties in implementation atlest with the given information. The required condition are already given by you.
code the same in the Transformer derivation to the require field.
i.e., for DM_AMT_BT the derivation will be if CDE1='DM' and CDE4='BT' then AMT Else 0.....
Atleast using Aggregator groupby CHAIN and find the maximum of all the other fileds(Or find the sum). So that all the 0s will be discarded and the AMT will be retained.
I dont find any dificulties in implementation atlest with the given information. The required condition are already given by you.
Code: Select all
if CDE1='DM' and CDE4='BT' then AMT
i.e., for DM_AMT_BT the derivation will be if CDE1='DM' and CDE4='BT' then AMT Else 0.....
Atleast using Aggregator groupby CHAIN and find the maximum of all the other fileds(Or find the sum). So that all the 0s will be discarded and the AMT will be retained.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 12
- Joined: Mon Jul 18, 2005 4:07 am
Thanx for replying Kumar...i've already implemented this logic.
But i was just wondering if we have any spcific stage to do the job.(tried exploring combine rows etc but cudnt make much headway)
Think of a scenario when u have a large no. of combinations for CDE1...CDE4 values. The transformer derivations wud look a bit dirty...isnt it?
Thanx anyway.
Chits
But i was just wondering if we have any spcific stage to do the job.(tried exploring combine rows etc but cudnt make much headway)
Think of a scenario when u have a large no. of combinations for CDE1...CDE4 values. The transformer derivations wud look a bit dirty...isnt it?
Thanx anyway.
Chits
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Not dirty, just large. You wouldn't gripe at using 76 local variables in a program would you? Then what's wrong with 76 stage variables in a Transformer stage (which become 76 local variables in the generated code)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.