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]

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

[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.

[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?

[RolandSchubert]

Hi @SH_94 ,

you could filter rows containing "IN" in uppercase and remove all characters to the left and right from the invoice number.

 

Let me know if it works for you.

Bet,

Roland

[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

[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

[Luke_C]

@SH_94 

You can change the version of the file by opening in notepad and editing the text: