Challenge #37: Parsing a Raw XML File

The link to the solution for last challenge #36 is HERE.

Using raw XML files as input can present some interesting challenges. The challenge is that the data is nested into the records in a way that requires you to extract it through parsing steps, sometimes drilling many levels into the data (Root and child levels). Alteryx makes this easier to do using the XML Parsing tool. We will explore the process in this exercise.

Use Case: A company receives customer purchase and shipping data on a weekly level based on web and catalog purchases. The company would like to analyze their customers and produce a profile by market by SKU. The challenge is that the data feed contains XML that needs to be parsed in order effectively analyze the data.

Objective: The column called customer_OuterXML contains the data that needs to be parsed into 25 unique fields detailing the customer contact information for both the “Bill To” and “Ship To” attributes.

Note: As of 9/11/2019, the Start file and Solution files were edited. Based on when you complete this challenge, you may see that the solutions posted here may reference a dataset that was previously available. Posted solutions (as files) using the previous dataset have been replaced with the Alteryx Academy logo to acknowledge that user's contribution that we can no longer share publicly.

challenge_37_solution.yxzp

challenge_37_start_file.yxzp

Parse

Preparation

Advanced

Data Analysis

Intermediate

Transform

Accepted answers

All comments

My solution

Spoiler

A little different, but similar approach. I like the start of the official solution.

Spoiler

Get the easier one here:

academy_logo (1).png

My solution : (although have not renamed the columns as per in the output )

academy_logo (1).png

Never done XML parsing before so I needed a some help on this one

academy_logo (1).png

JonathanAllenby

Here's my solution to challenge 37!

academy_logo (1).png

Challenge #37, done.

Did not use XML Parse Tool, I chose to parse this as a flat file.

Spoiler

Cheers,

I went a little crazy trying to dynamically rename and order the fields to match the output!

Spoiler

Tried to use as few tools as possible. Used the ability to rename in Join Multiple tool to correct names. Naming connections before joining helps keep track of inputs to select for Join Multiple output.

Spoiler

Workflow 37.GIF

Here's my solution. Not quite as efficient as the provided solution but a great learning exercise.

academy_logo (1).png

Solved

academy_logo (1).png

academy_logo (1).png

Nice XML practice

academy_logo (1).png

I normally dig XML, but this was my first using the XML parsing tools in Alteryx, and I am sure, with some time, I will love then ❤️

But this was hard ">💔 and not that straight forward as I thought, but here it is

Spoiler

Okay, I tried to break it down into smaller pieces, but afterwards I found out, that this is not necessary.

I also tried to make the re-naming more dynamic, but that was a half-a** attempt, since I had to do some renaming in the last join anyway 😒

First attempt to parse XML

First attempt to parse XML

Still climbing to Lhotse Face

/Verakso

My Solution

academy_logo (1).png

My solution, I used Dynamic Rename tool to add the column prefixes.

academy_logo (1).png

Spoiler

Weekly Challenge - 37.PNG

Challenge completed. Hadn't used the XML parse tool before so there was a bit of trial and error in there.

Used the Dynamic rename tool for the renaming of the fields, as opposed to the select tool

academy_logo (1).png

Spoiler

Done with a little assistance from the provided solution 🙂

Well this was surely nightmarish!!

The aim is to parse this horrible file structure without having the option to rely in an input data tool (or can it somehow be done to write the .yxdb to a .xml file because I was not successful in doing that)

Here is my solution (step by step manually going through all the tags until I finally brute forced my way into something that might be considered a solution (can anyone in the community build something that parses the entire thing regardless of the amount of nested tag levels?)

Spoiler

nickjastrzebski

A nice data clean up challenge

Spoiler

Understand XML a lot better now - thanks for the challenge!

academy_logo (1).png

Hannah_Lissaman

My solution - this was a very useful exercise since I've never used XML parse before.

academy_logo (1).png

Solution attached

academy_logo (1).png

academy_logo (1).png

Johnny_Analytics

Spoiler

piotrzawistowski

academy_logo (1).png

academy_logo (1).png

A tough challenge, but good introduction to using the XML parsing tool,

Spoiler

Week 37.PNG

Solution attached.

Thank you.

academy_logo (1).png

Here's my solution and I found the XML parsing example workflow very useful here!

Spoiler

Challenge 37.PNG

Quick Links

Tags

This months top contributors

erich_shiino 280