Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to extract data from messy table format?

Inactive User
Not applicable

Hi all,

 

I've got an output from some payroll software that's got nulls in all the wrong places. I've attached a workflow which contains a mock-up of my data structure and what I want the output to look like (also see screenshots below). I just want to summarise the data like the output (see screenshots below).

 

Please help, I've pretty spent all day on this and it's driving me nuts.

 

I want to take this messy table style input:

 

Messy Input.png

And turn it into the following useable output:

 

Desired Output.png

 

My initial thoughts were that I'd just need to transpose the data, keeping the "Employee ID" and "Name" as key columns and take it from there, but it's immediately created as new problem (see below) because of the structure of that data, where the rows with values don't have an employee ID or a name.

 

I'm not even sure if this is the right avenue to be exploring, but I've tried to solve this problem by populating the nulls in the employee ID and name columns with the ID and name. I've tried generating rows, multi-row formulas, appending fields to no avail - I'm well and truly stumped with this one.

 

I'm not even sure if this is the right way to be approaching or thinking about the problem.

Data Transposed.png

Any help would be very appreciated!

 

James

4 REPLIES 4
atcodedog05
22 - Nova
22 - Nova

Hi @Inactive User 

 

Here is how you can do it.

Edit: updated workflow with order.


Workflow:

atcodedog05_0-1642008173192.png

 

Hope this helps : )

 

gabrielvilella
14 - Magnetar

Hi, you need first to fill out those nulls for the ID and Name columns, then you can use the transpose. See if my solution is what you are looking for.

Inactive User
Not applicable

That's amazing, two neat solutions - thank you both so much!

 

It was the expression in the multi-row formula that was tripping me up, I need to get some more practice with those.

 

Thanks again.

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @Inactive User 

Cheers and have a nice day!

Labels