Mapping multiple rows to a single row

[chitrangadsingh]

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

[legendkiller]

Can you elaborate more as how output is getting derived from input.

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

[chitrangadsingh]

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.

[kumar_s]

Hi,
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 

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.

[ray.wurlod]

Search the forum for "vertical pivot". The stage variables technique works well both in server and parallel jobs.

[chitrangadsingh]

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

[ray.wurlod]

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)?