Hi Everybody,
Iam trying to Have a join in between two tables based on a common field(Say CUST_NO).
I want to pick up Other field (Say ITEM) in Table 2 based on the Key between these two tables. But the problem is the Common field (CUST_NO) in the second table is not Unique. So when i try to test this join for one CUST_No say 123, i get one record from Table 1 and 2 from Table two resulting in 2 records after Join. If i observe these 2 records all the values are same except the ITEM. My requirement is to have a single record With both these ITEMS under field ITEM1 and ITEM 2 in my output.
Or is there any other way to get a single record with the values of these two Items in the same record.
Thanks In adv..
Problem in Joining
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Hi,
You need to 1st sort CUSTNO then using stage variable you need to store CUSTNO and ITEM , and Compare old CUSTNO with current CUSTNO if it is equal then map old ITEM to ITEM1 and new to ITEM2 in tarnsformer ,use aggregator berfore target in that say MAX(ITEM1) and MAX(ITEM2) group by other columns..I think this may help you in solving problem..
Thanks,
Anupam
You need to 1st sort CUSTNO then using stage variable you need to store CUSTNO and ITEM , and Compare old CUSTNO with current CUSTNO if it is equal then map old ITEM to ITEM1 and new to ITEM2 in tarnsformer ,use aggregator berfore target in that say MAX(ITEM1) and MAX(ITEM2) group by other columns..I think this may help you in solving problem..
Thanks,
Anupam
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need to apply a vertical pivot to the joined data (which contains multiple rows per CUSTNO). Search the forum for vertical pivot; there are several techniques available. Pivot stage is not one of them.
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.