Data Filtering in the alteryx

[SH_94]

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?

[Maskell_Rascal · Accepted solution]

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

[SH_94]

Hi @Maskell_Rascal ,

Thanks a lot for your help. It really very detail and clear explanation. I have few questions as below:

1. Previously we use CSV file to import the file, so currently we choose ASCII, is it because of the file being presented?

2. If i have multiple of txt file to import into the alteryx, may i know do you have any suggestion that i can import once and allow the alteryx to read all the file?I try a lot of macro file that being shared online but it always fails.

3.May i know how u open the flat file layout as per your second screenshot in your post? Could you guide me on which button/icon i need to click?

4.Could you briefly explain the purpose on the formula tool used for the screenshot below? May i know what is the meaning of if =1 and if =0?

5. Could you briefly explain on the difference between Replace and REGEX_ Replace in this case? Any different or impact if we use Replace? May i know normally how u determine whether u need to use Replace or REGEX_ Replace to add into the canvas?

6. For this Multi-row formula as per screenshot below , is it same with the excel formula fill in the blanks?

7.I like the format of table that you rendered out in the excel. Could you please share with me the complete workflow ( with the table setting included).

Thank you again for your help.

[Maskell_Rascal · Accepted solution]

Hey @SH_94 

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

1. I switched the input to ASCII in order to use a fixed width input setting. 
2. 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
3.  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
4. 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.
5. 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
6. More or less, yes. I'm just filling down anything that's missing using an IF statement. 
7. 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

[SH_94]

Hi @Maskell_Rascal ,

I have run the imported file in the alteryx and it appears the error as below:

May i know how should i fix this in this case as i have shortened the name of the file.