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

Incorrect data split

Duszko
7 - Meteor

Hello,

Within my dataset some cells contains quotation marks within its values (I guess it stands for inches). It's like:

 

 

 

sthsthsth 1.22",1"

 

 

 



due to this quotation mark Alteryx cannot recognize comma as a column delimiter and incorrectly splits columns. How can I fix this if input is an excel file (not csv)?

10 REPLIES 10
ShankerV
17 - Castor

@Duszko 

 

If I understand your requirement correctly. Alteryx will read the excel file in one column.

 

Post which you can use Text to columns like below to get the desired result.

 

ShankerV_0-1673502620771.png

ShankerV_0-1673502668689.png

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @Duszko 

 

Another way is to use Regex function also like below.

 

ShankerV_0-1673502805829.png

(.+)\s(.+),(.+)

 

ShankerV_1-1673502816323.png

 

Many thanks

Shanker V

 

Duszko
7 - Meteor

My input is more like this:

Duszko_0-1673503196450.png

 

So there are around a million rows; among them are some with this incorrect split. I need to fix the split so that all columns are mapped correctly.

Should I filter out those incorrect rows, use one of your solutions and then Union or is there any other way?

 

ShankerV
17 - Castor

Hi @Duszko 

 

Can you give 10 samples in the excel format with all permutation and combination, so will propose the solution for the huge dataset to work accurately.

 

It will be easy to load and test from my end also.

ShankerV
17 - Castor

Hi @Duszko 

 

Check whether it works for you. Hope you are expecting the result like this.

 

Output:

 

ShankerV_0-1673504177652.png

 

Input was:

 

ShankerV_1-1673504194491.png

 

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @Duszko 

 

Step 1: Input

 

ShankerV_0-1673504260765.png

 

Step 2: Record ID

This will help to keep the records in order post we do the modifications and union the result.

 

ShankerV_1-1673504324307.png

 

Step 3:

ShankerV_2-1673504346770.png

ShankerV_3-1673504355279.png

 

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

 

Many thanks

Shanker V

 

ShankerV
17 - Castor

Hi @Duszko 

 

Step 4:

ShankerV_0-1673504396370.png

 

ShankerV_1-1673504404281.png

 

Step 5:

ShankerV_2-1673504423485.png

The below order is more important to display the exact result.

ShankerV_3-1673504462114.png

 

Step 6: Select tool

ShankerV_4-1673504481959.pngShankerV_5-1673504497332.png

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

 

Many thanks

Shanker V

 

 

Duszko
7 - Meteor

Hi @ShankerV 

The data is confidential so I cannot give you an exact sample. The file I've attached is created by me to present the issue.
Luckily there is only one permutation (if I understand correctly what permutation is 😅 => pattern that causes the error).


ShankerV
17 - Castor

Hi @Duszko 

 

Step 7: 

 

ShankerV_0-1673504557965.png

 

ShankerV_1-1673504575635.png

 

Final output:

ShankerV_2-1673504589811.png

 

If this helps, please like this post and mark it as a solution. If you have any other questions, please let us know.

 

Many thanks

Shanker V

 

Labels