String Concatenation based on Keys

[dganeshm]

052288064081042322OCT08TUL300AA3151MAINT ENTRY. AIRCRAFT NEEDS LMP DOWNGRADE DUE TO AUTOLAND IN 60 DAYS ******************** MEL
052288064081042322OCT08TUL300AA3151 *RESTR TO CAT I*. DUE TO 60 DAY EXCEEDENCE OF THE F.C.C
The key here is
052288064081042322OCT08TUL300AA3151

based on this I wanted to concatenate the Text field... please suggest on how to do this..

[ray.wurlod]

Make sure that your data are partitioned on the key.

Sort the data by key, generating a Key Change column. If the data are already sorted, indicate this fact in the Sort stage.

In a Transformer stage stage variable maintain the concatenated string.

Code: Select all

If KeyChanged Then input.String Else svConcatenatedString : " " : input.string

Downstream of the Transformer stage use a Remove Duplicates stage to preserve only the last record for each key value.

[qt_ky]

Downstream of the Transformer stage use a Remove Duplicates stage to preserve only the last record for each key value.

If you're on version 8.5 or higher, could you use the LastRowInGroup() function in the Transformer constraint to avoid a Remove Duplicates stage? You may also be able to avoid generating or testing a key change column, if you tested on LastRowInGroup() and altered the stage variable logic a bit. I think it should be possible, but I haven't tested it.

[Kryt0n]

Or the pivot stage? (Maybe assuming 8.5 again as can't remember what 8.1 offered)

[dganeshm]

Created a CHECKSUM based on the keys and then used a stage variable in the Transformer stage and concatenated the strings. And used a remove duplicates stage to retain the last one.