Adding rows to a single column

[swadeep]

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

[anbu]

Use stage variables

Code: Select all

PrevKey = CurrKey
CurrKey = Key
CurrData = If PrevKey = CurrKey then PrevData : Data else Data
PrevData = CurrData

Use Remove Duplicates to retain the row which has longest Data.

Also you should run this job in 1 node.

[jcthornton]

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.

[swadeep]

Thank you very muck. It works.

[ravireddy25]

Use stage variables

Code: Select all

PrevKey = CurrKey
CurrKey = Key
CurrData = If PrevKey = CurrKey then PrevData : Data else Data
PrevData = CurrData

Use Remove Duplicates to retain the row which has longest Data.

Also you should run this job in 1 node.

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

[ray.wurlod]

Use Len() functions to compare lengths.

[ravireddy25]

Use Len() functions to compare lengths. ...

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

[ray.wurlod]

Generate a column containing the length and aggregate preserving MAX.

[ravireddy25]

Generate a column containing the length and aggregate preserving MAX. ...

Hi,
Got it Thanks