Dynamic Filtering where data in different postions

[zodiac01]

Hi All,

I am trying to filter out certain information where the data is below certain keywords in certain instances and on the same line in other instances. I am trying to extract the data as circled in the attachment. 

For example, 'Type/Form'-data is below

'Account Number'-data is below

'Currency'-data is below

'Balance:'- Data is next to

I would really appreciate some assistance with this regard.

Thank you 😄

[CharlieS]

Is the plan to reorganize that information into a data scheme with field names and rows of data? Something like this:

Otherwise, I don't see a reliable way forward in the current format. The per-record filter method would not correctly associate records without a grouping field, and identifying the appropriate content to filter on would be difficult without a data scheme/structure. 

[AbhilashR]

Hi @zodiac01, I mocked up some data to replicate your source file and built a sample solution for you to build upon.

I hope this helps.

[zodiac01]

Hi @CharlieS ,

Yes this is the exact format I need to get it in, The only issue I have is that all the data is in one field as shown in the picture attached.

Kind regards

[atcodedog05]

Hi @zodiac01 ,

The picture seems to be missing.

[zodiac01]

Hi @AbhilashR ,

Thank you for your response and for the sample workflow, I really appreciate it! This is the format I need to get it in. However the issue that I have is that all the data is in one fields as shown in the picture attached, I am not sure how to approach that.

Kind regards

[atcodedog05]

Hi @zodiac01 

Can you provide some sample data, logic and expected output. I can prep up a workflow for you.

[zodiac01]

Hi @atcodedog05 ,

Thank you for your response and for being willing to help

I have attached some sample data. The input data comes in as an alteryx database and all records are in one field

. The objective is to get all the records of this instance in one line i.e to get the type/form, account number, currency and balance in one row.

[atcodedog05]

Hi @zodiac01 ,

Here is a workflow for the task.

Input: File given by you

Output:

Workflow:

If i am not wrong this output is a template which takes 6 rows. If you have multiple entries like this you can use Record Id tool and formula (Record ID-1)/6 to create batch of 6 and load it to a macro to extract rows.

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍