How to fix data shifting?

[Aria_S]

Hi there,

I have a .txt file which has a lot of data shifts. The lines are breaking to next rows. I have created a dummy data that is similar to my data set. Is there any way we could spot and concatenate row 4,6 and 8 to 3,5 and 7 respectively?

ID	TITLE
1	045|XYZ|23.00|NULL|XYZ|AA
2	075|ABC|23.00|NULL|ABC|AA
3	049|XYZ|23.00
4	|NULL|XYZ|AA
5	077|ERT|23.00
6	|NULL|ERT|BB
7	060|BBB|23.00|NULL
8	|BBB|CC

The output should look like this:

ID	TITLE
1	045|XYZ|23.00|NULL|XYZ|AA
2	075|ABC|23.00|NULL|ABC|AA
3	049|XYZ|23.00|NULL|XYZ|AA
4	077|ERT|23.00|NULL|ERT|BB
5	060|BBB|23.00|NULL|BBB|CC

Thanks,

Aria

[Emil_Kos]

Hi @Aria_S,

I used this post by @kat in order to build this solution

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Concatenate-shifted-data/m-p/316783/hi...

She had a splendid idea and I needed to make only a few tweaks to make it works.

The output:

If this solution was helpful for you please mark it as a solution. 

[AngelosPachis]

Hi @Aria_S ,

I used a formula tool to Regex count the number of | in each record.

Then, with a multi-row formula tool, I have created a running total of the | count, and since you should have 5 | in each record (because that's how many fields you should have) I created groups using the mod function.

So then I had the field that I can use to group on, and with a summarize tool I could then proceed to concatenate the different fields.

Let me know if that worked for you or you have any questions on the workflow.

Regards,

Angelos

[AngelosPachis]

Oh there was already a post out there, didn't know that.

Guess the only difference is the modulo function, but that's a tiny detail.

[vizAlter]

Hi @Aria_S - Try this solution: