Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to fix data shifting?

7 - Meteor

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?

 

IDTITLE
1045|XYZ|23.00|NULL|XYZ|AA
2075|ABC|23.00|NULL|ABC|AA
3049|XYZ|23.00
4|NULL|XYZ|AA
5077|ERT|23.00
6|NULL|ERT|BB
7060|BBB|23.00|NULL
8|BBB|CC

 

The output should look like this:

 

IDTITLE
1045|XYZ|23.00|NULL|XYZ|AA
2075|ABC|23.00|NULL|ABC|AA
3049|XYZ|23.00|NULL|XYZ|AA
4077|ERT|23.00|NULL|ERT|BB
5060|BBB|23.00|NULL|BBB|CC

 

Thanks,

Aria

14 - Magnetar

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:

 

Emil_Kos_0-1610569900340.png

 


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

 

Alteryx Certified Partner

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.

 

AngelosPachis_0-1610570014869.png

 

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.

 

AngelosPachis_1-1610570056633.png

 

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

 

Regards,

 

Angelos

Alteryx Certified Partner

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.

12 - Quasar

Hi @Aria_S - Try this solution:

vizAlter_0-1610570811351.png

 

 

Labels