Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Concatenate shifted data

hanz_e
6 - Meteoroid

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 haveData 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 needData 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

 

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus

@hanz_e,

 

I'd like to see the original data.  It is possible that the newline (\n) character is present and if removed would fix the problem for you.  Otherwise you'll need to use a Multi-Row Formula and if the Right() function is used to search for a period (.) at the end of the record doesn't exist, then you'll be concatenating [Row-1:Field] + [Field] data together.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
danilang
19 - Altair
19 - Altair

Hi @hanz_e

 

Your output contains a character that's not in your input.  

 

"Share your smile with the world" Line 34 becomes

 

"Share your smile with the world." Line 34+35 There's an extra period "." at the end.  Is this just a typo on your part when you were creating the sample output data, or something the workflow has to determine?

 

Dan

hanz_e
6 - Meteoroid

Hi @MarqueeCrew,

 

First of all, sorry that the actual data do not contain any special character at the end of each line to so that the workflow can identify. It was included when I copy a normal sentence to make the sample data.

 

The the .csv file that I input is similar to the given sample data.

 

 Raw .csv fileRaw .csv file

Each raw was supposed to have 7 columns with 6 pipe delimiters. But the data have shifts at rows '3' & '7' where one field is split into another line. Thus, when I input this file with delimited input, it produce shifted data with rows having less number of delimiters than actual number of delimiters per line. I have created the workflow to find out the line numbers at which the shift occurs and the shifted data occupies. In this example, I need to automatically group the line numbers '3', '4' & '5' into one and '8' & '9' into another and then concatenate the data in the highest line number of each group to the previous line till it reach the lowest line number of each group.

 

Hope this explains the help I need.

hanz_e
6 - Meteoroid

Hi @danilang,

 

The actual data do not contain any special character at the end of each line. It was just included when I copy a sentence to make this sample data.

 

I guess the only way to solve this issue is based on the line number. For reference, I have shared the detail raw data in another post.

 

Sorry for the confusion.

kat
12 - Quasar

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 |.

concat string.PNG

  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

hanz_e
6 - Meteoroid

Hi @kat,

 

Thank you so much for the help.

 

The workflow works perfectly for the issue.

 

Will definitely reach out to you if anything comes up.

 

Hanz 

Labels