Adding rows to a single column
Moderators: chulett, rschirm, roy
Adding rows to a single column
Hi I have a scenario below, plz help me with the solution.
Input
-------
Key Add
1 3456 ABC lane
1 Apt 544
1 Charlotte NC
1 28217
2 101 ABC lane
2 Apt 467
2 Dallas Texas
2 75252
Output
-----------
Key Add
1 3456 ABC lane, Apt 544, Charlotte NC, 28217
2 101 ABC lane, Apt 467, Dallas Texas, 75252
Thanks in Advance
Input
-------
Key Add
1 3456 ABC lane
1 Apt 544
1 Charlotte NC
1 28217
2 101 ABC lane
2 Apt 467
2 Dallas Texas
2 75252
Output
-----------
Key Add
1 3456 ABC lane, Apt 544, Charlotte NC, 28217
2 101 ABC lane, Apt 467, Dallas Texas, 75252
Thanks in Advance
Use stage variables
Use Remove Duplicates to retain the row which has longest Data.
Also you should run this job in 1 node.
Code: Select all
PrevKey = CurrKey
CurrKey = Key
CurrData = If PrevKey = CurrKey then PrevData : Data else Data
PrevData = CurrData
Also you should run this job in 1 node.
You are the creator of your destiny - Swami Vivekananda
-
- Premium Member
- Posts: 79
- Joined: Thu Mar 22, 2007 4:58 pm
- Location: USA
With the definition of the input given, I hope the correct order is guaranteed on the input, otherwise the addresses could get a bit jumbled.
On a second note, is there a reason for recommending just one node? It should work just fine as long as your partitioning (I would probably use Hash partitioning on just the Key field) keeps all rows with the same key together.
On a second note, is there a reason for recommending just one node? It should work just fine as long as your partitioning (I would probably use Hash partitioning on just the Key field) keeps all rows with the same key together.
Jack Thornton
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
-
- Participant
- Posts: 59
- Joined: Wed Dec 31, 2008 5:49 am
HI,anbu wrote:Use stage variablesUse Remove Duplicates to retain the row which has longest Data.Code: Select all
PrevKey = CurrKey CurrKey = Key CurrData = If PrevKey = CurrKey then PrevData : Data else Data PrevData = CurrData
Also you should run this job in 1 node.
I have one doubt like we can remove duplilcates based on key but how to retreive the row which has longest data.
Thanks In Advance
Ravi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 59
- Joined: Wed Dec 31, 2008 5:49 am
Hi,ray.wurlod wrote:Use Len() functions to compare lengths. ...
sorry to ask again, i can use Len function but if we have data like
key data length
1 ------ 5
1 --------- 7
1 ----------- 8
2 ---------- 6
2 ------------ 7
2 -------------- 8
and my output should be like
key data length
1 ------------------ 8
2 ------------------ 8
How we can achieve this?
Ravi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 59
- Joined: Wed Dec 31, 2008 5:49 am