Alteryx Designer Desktop Discussions

ForMcKHG · ‎03-22-2021

Hi,

I am fairly new to Alteryx so I am not sure if the below scenario can be achieved:

I have a fixed width file that needs to be split into multiple tables based on characters that act as key.

Let's say, each row in the file is 50 characters. I have characters 8 to 10 that act as key and on the basis of the value of contained in these three places, I need to split the file into multiple.

Example data:

0000011A1 ,XYZ,02,C967 ,70ABC,2-10-01 ,IKL0 ,BC6Q ,24561
0000011B1 ,ABCEF,G 04,53 ,32, ABBCD,24561
0000011C1 ,9430,20 QWE,RTYF ,22 po,iu ,24561
0000011D1A, PQRST ,PQRS,TAB,CDEF ,JKL ,746936,59231

Characters in bold (places 8-10, space included) define how the file should be split.

There are no commas in the original file, I am adding them here to explain how the column fixed widths change on the basis of the bolded characters.

I was not able to find a way for this on the community so far, any help would be appreciated.

TIA.

ChrisTX · ‎03-22-2021

If the characters 8 - 10 always represent your Table Id, you should be able to use a Formula Tool with a substring formula, to create a field for your Table Id. Then use the Output tool and change the output table name based on your new Table Id field.

See attached.

Chris

ForMcKHG · ‎03-22-2021

Hi Chris,

Thank you for the reply.

That would help with the file split into multiple tables.

I am also wondering how to tell Alteryx to delimit each row differently. The column widths change based on the substring 8-10.

I tried reading the file as a fixed width file but that only helps until characters 10. The first 10 characters form 3 columns. The remaining columns are to be split on the basis of 8-10. If value of 8-10 is "A1 " then the row should split into columns like so: XYZ,02,C967 ,70ABC,2-10-01 ,IKL0 ,BC6Q ,24561.

If value of 8-10 is B1, then the row should split into columns like so: ABCEF,G 04,53 ,32, ABBCD,24561.

So on..

Any insights would be helpful.

pedrodrfaria · ‎03-22-2021

@ForMcKHG

Are there spaces or no commas and no spaces?

The logic would probably be to use Regex to clean up the ID field and then apply a IF function to break out the characters. It would be something manual unless there is some logic we can apply to separate them.

If there is a space, we can use the space as a text delimiter and use the text to columns to break out into different columns.

ForMcKHG · ‎03-22-2021

Hi,

The spaces are part of the text and don't act as delimiter. The commas are not a part of the original text. I added them to describe how the column width of each row is dependent on substring value 8-10.

Attached is an example of what is expected as results.

Any insights would be appreciated.

Thanks.

pedrodrfaria · ‎03-22-2021

@ForMcKHG

Are we only dealing with these 4 possible variables? A1, B1, C1, D1A? Or are there more?

Because you can set up a manual logic for these 4 variables. But I do not see any dynamic logic being applicable right here.

ForMcKHG · ‎03-22-2021

There are more possible values. I have a huge input file that I need to read.

@pedrodrfaria and @ChrisTX, Thank you for your thoughts so far.

pedrodrfaria · ‎03-22-2021

If there are many variables and they do not follow any kind of logic, it becomes hard to get this done.

When you are doing it manually, how do you know to which columns to break out the data? There has to be some kind of logic besides "it is what it is".