Parsing multiple fields (with different patterns)

[vplante]

Hello,

I am trying to figure out the following issue

Input data:

Name	Format	System	Identifier 1	Identifier 2
Phone number	FLOAT	System A System B System B	PHONE 44_PHONE 55_PHONE	NUMBER 44_NUMBER 55_NUMBER
Address	VARCHAR VARCHAR2	System A	ADDRESS FULL_ADDRESS	STREET STREET_NAME

Desired output:

Name	Format	System	Identifier 1	Identifier 2
Phone number	FLOAT	System A	PHONE	NUMBER
Phone number	FLOAT	System B	44_PHONE	44_NUMBER
Phone number	FLOAT	System B	55_PHONE	55_NUMBER
Address	VARCHAR	System A	ADDRESS	STREET
Address	VARCHAR2	System A	FULL_ADDRESS	STREET_NAME

Note the following patterns:

- Name is unique to an attribute

- Format / System / Identifier 1 / Identifier 2 can vary but should be mapped with the corresponding value on the same line (ex: System A / PHONE / NUMBER)

- If there is only one value for format or system, it means that it will remain the same for all the identifers.

Thank you so much

[wdavis]

Hi @vplante 

I have built a workflow which hopefully addresses this problem. By using Multi-Row Formulas you are able to populate records down and ensure that all information is carried into the output you desire.

Let me know if this was what you required, or if I have missed something here!

Kind Regards

Will

[vplante]

Hi wdavis,

Thanks for your answer, there is a point missing thought... you have already parsed rows 1 & 2 into 5 rows.

I am also looking at way to parse multiple columns at once.

Thanks.

[wdavis · Accepted solution]

Hi @vplante 

Okay, the best way to be able to parse multiple fields at the same time would be to use a Transpose to get all of your values into one column, you can then add any necessary steps you require, before reformatting the information using a Cross-Tab. The workflow has now been updated to include these steps.

[vplante]

Thanks wdavis, this is confirming my initial findings. I guess this is the way to go!