Take a value and add it to a new column on each line

[Bigmonki]

Hi All, 

The attached file is a *.xml file layout that I get from a system file. I can extract it and load it in to Alteryx, but I need to take a value and use that in a new column to append each record with a branch code!

So in the xml file, I need the KeyName Company value (02, 03 etc.) to append to each subsequent record as it loads in to Alteryx

So the file looks something like this

When it reads the "Company" code, each subsequent line until it reads the next "Company" code needs a new field called branch with the relative company code. the code must be a minimum two characters long in the output, I assume I can use a formula to pad out the field and add a 0 to the left if required.

TIA

[jacob_kahn · Accepted solution]

This is a great example use case for the Multi- Row formula tool.

I've attached a packaged workflow with your data!

Please like and accept this reply if it helps you 😉 

As well, let me know if you need help understanding what I did 🙂

J

[grazitti_sapna · Accepted solution]

Hi @Bigmonki , please follow the attachment and screenshot, I hope it helps!

[Bigmonki]

Hi @grazitti_sapna 

Brilliant, whats good is you spend hours looking for a solution and find loads of stuff you didn't know about. Then when you reach out for help, there is someone who knows exactly where to look.

The Multirow formula was the perfect solution.

Much Kudos for solving a problem I have been trying to fix for a couple of weeks now.

🙂

[Bigmonki]

Hi @jacob_kahn 

Yep, Mutlirow formula.

I seem to be fine with most stuff, but the two things I really struggle with are REGEX, PARSING and Multirow formula.

The solution when you present it looks so simple, my head just doesn't compute. Maybe that why when I left school I became a truck mechanic, hitting something with a hammer was far more satisfying.

🙂

[grazitti_sapna]

Hi @Bigmonki , thank you so much for appreciation, always happy to help.

[jacob_kahn]

A truck mechanic! I love it.

I actually first went to school to be a historian - thankfully, the writing skills have been useful in my current job - but my months in the library didn't necessarily payoff with regards to my thesis. 

That is okay, RegEx totally blows my mind most of the time. If you'd like to see something really cool with RegEx, learn more of its functionality, tips and tricks, etc. I would refer you to this Weekly Challenge: here.

This challenge opened my eyes to the capacity RegEx can parse out data.

Now with Alteryx's new Intelligence Suite, I've started processing tax return forms with RegEx and it is amazing! I wish you only luck and success with your work!

Feel free to connect on LinkedIn, if you hover over my profile icon.

Sincerely,

J