Data Filtering in the alteryx

Hi Community,

I have imported the txt file into alteryx and performed the text to column formula. Due to the different number of spacing in between , it created the result data A as shown below. However, i want the data that begin with IN only and plan to filter the text with the beginning of IN and tabulate into new column again as shown below.

May i know if anyone of you know how to fix this?

Common Use Cases

Accepted answers

Maskell_Rascal

@SH_94

Per our PM conversation, I've attached non-zipped versions of the Macro and Workflow for you to use with it downgraded to the 2019.13 version.

Again, please keep in mind that if the macro is not installed on your computer prior to you opening the workflow, it will not be possible to use it.

Thanks!

Phil

CReW Delete Empty Fields.yxmc

New Workflow5.yxmd

Different space in the words.txt

Maskell_Rascal

Hey @SH_94

Now that I see all the different configurations how your data is being presented, I do have a solution that should account for all the differing variables.

This new solution reads the file in as a fixed width file to break out the Customer Number, Customer Name, Batch, and all other fields first. The input is also including the file name as a field, so you can then change that at the end to your desired country as you mentioned.

I'm also importing settings for the flat file in order to make sure that the fixed settings are consistent.

Once the files are read in, I'm Joining them all together and then using a formula tool to cleanup some weird issues with the fixed width and also some character replacements to solve for some field conversion issues later in the workflow.

I then added a multi-row formula tool to add the batch name where missing to all records.

From here the process is similar to the last workflow I sent you. Data Cleanse, Text to Columns, and Dynamic Rename. The only difference is that I added a Select tool to convert the number fields to doubles, and then used a Filter tool at the end to filter out null values from one of the currency fields.

This now leaves you with just the records needed in the output.

Attached is a zipped version of the workflow, which I think I was able to downgrade to your version. If not, I have also attached all the necessary components to the workflow individually, but you'll need to map them on your end. Please note that you'll need to change the extension name of the Fixed Width Setting file from .txt to .flat

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

Text File Input-Parsing - Final Solution.yxmd

Fixed Width Settings.txt

Text File Input-Parsing - Final Solution.yxzp

Maskell_Rascal

Hey @SH_94

I'll answer these in the order you typed them.

I switched the input to ASCII in order to use a fixed width input setting.
You can input multiple files at the same time by using an asterixis wildcard identifier. Here is more info on the Input Tool: https://help.alteryx.com/current/designer/input-data-tool
The screenshots I provided show which selections are needed to navigate to your .flat file. Here is more info on Flat File Layouts: https://help.alteryx.com/current/designer/flat-file-layout
Since we are using a fixed width input, some of your dates were coming through with the last digit for either 2020 or 2021 cut off. This formula checks for that and corrects the field as needed.
REGEX_Replace is just my default function when I'm looking to replace a word/character. You could also use Replace. Here is more info on String Functions: https://help.alteryx.com/current/designer/string-functions
More or less, yes. I'm just filling down anything that's missing using an IF statement.
This formatting wasn't built in Alteryx. This output was just an excel dump. I opened the file and changed the formatting so you could see all the fields. You could probably build out the same thing in the Alteryx Reporting tools.

Thanks!

Phil

All comments

Maskell_Rascal

Hi @SH_94

Can you share what the data looks like before you input into Alteryx and Text to Columns? It would be easier to see how the raw data looks in order to come up with a solution.

Thanks!

Phil

SH_94

Hi @Maskell_Rascal ,

Kindly refer to the file attached below for your reference.I have tried a lot of tools to play around but not sure what is the best tools to fix it when importing into the Alteryx

TRY.xlsb.txt

echuong1

You can pivot the data with a transpose, then filter for the values that start with "IN." I added a record ID to know which row the items came from, but you might not need that depending on your requirements.

New Workflow9.yxmd

SH_94

Hi @echuong1 ,

Thank you for the workflow provided. Do you know if there is any better way to build the workflow and capture the information from the txt file below?

TRY.xlsb.txt

Maskell_Rascal

Hi @SH_94

This might be more than what you're looking for. Attached is a workflow that will pull in the file, split to columns, and rename all the fields as seen in the text file.

Thanks!

Phil

New Workflow4.yxzp

SH_94

Hi @Maskell_Rascal,

I can't open the file as my version of alteryx is 2019.13. Could you save the macro and worklfow in this version?

Thank you,

Maskell_Rascal

Hey @SH_94

I removed the macro I was using. It a CReW Macro that removes any Null or Empty columns. I typically use it anytime I do a Text to Columns, so I don't have to know/adjust the number of columns to split out.

Thanks!

Phil

New Workflow4.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167