Concatenate shifted data

hanz_e

Hi all,

I am facing a problem with frequent data shift when a .csv file imported with delimited input. The data is somewhere broken and shifted to the next line, therefore throws an error in the workflow. This issue is caused when the data is extracted from source systems, and we do not have any control over this process.

I have given the sample data with the line numbers at which the shift occurs below :

Data I have

In this data, a sentence is broken into different adjacent lines and I need these to concatenate into a single line based on the line number.

The output that i need to get is as shown below:

Data I need

The idea is to find out the adjacent line numbers at which the line shift occurs and concatenate into the previous line so that the initial line at which shift starts gives the proper sentence.

Note:  It will be preferred to get a solution which automatically groups out the adjacent  line numbers at which the shift occurs and then concatenate the largest line number with the previous one so on. I am trying to automate this process since it is a common issue I face in my work.

Thanks in advance

kat

Hi @hanz_e

Here is a solution that will work on the data you provided. It won't work if the data is split into new lines in places where there is a |.

1. I counted the number of pipes in a row
2. I created a running total that reset every time we reached 6 pipes (the max)
3. I created a grouping field, based on the running total, where the grouping reset after each running sum of 6.
4. I then concatenated the data based on the grouping identified above, with a space (\s) separating the data.

Hope this works for you! Please do give a shout if you need some more help on it or if there's other scenarios.

Cheers

Katrin

concat string.yxmd