Unmerging and copying to be able to transpose

[regisb]

Hi! 

I have a new problem with the Alteryx(attached the excel for input and desired output): 

1. I have a matrix with 2 different indicators per country, but not all countries have the 2, some only have 1 (see Taiwan). The countries are merged for both columns on the Excel, but get unmerged when uploading. 

2. I want to convert this to a table such as the one on the "output" tab (4 columns: Country, date, Equity, Debt). But when I upload to Tableau I get the following image: 

What is the best way to copy the countries on the left to fill both equity and debt columns (i.e. ChinA is copied to F3), but  without affecting the countries that only have 1 dataset (i.e. Taiwan)? 

Thank you! 

[ZacharyM]

You have to flip the data around and join together the two rows that contain the data, then dynamically rename the data back, but the attached does what you need.

Take a look and let me know if this isn't quite right - if this or another post solves your issue, please remember to mark a solution as accepted so the community knows you've been helped!

Best,
Zak

[jdunkerley79 · Accepted solution]

I attach my go:

First read in the headers to get country and field. 

- In the Input tool, Skip the first 2 lines of the sheet (Data Starts on Row 3)

- In the Input tool, Tell Alteryx first row contains data

- Sample the first 2 rows

- Transpose these rows

- You can then use a multi-row formula tool to fill the nulls in

- Finally Get Country and Field values using a summarise.

Next, filter the null dates and transpose the data so it is row based

Then join to the headers and cross tab back

Sample attached

[CharlieS]

Nice work @ZacharyM and @jdunkerley79!

[regisb]

@ZacharyM

Could you send in the older version? I believe I have 2018.3 and I get an error when trying to open... 

Thanks! 

[regisb]

@jdunkerley79

Thank you! I'm double checking with the data, but seems that this solution works! :) 

[ZacharyM · Accepted solution]

Hey @regisb!

Sorry about that. Due to some pretty fundamental changes to how we encrypt data in 2018.4, (as you've noted) 2018.4 is not backwards compatible with previous versions. 

I actually had to grab and install a parallel version of Alteryx 2018.3 and re-create the workflow, so apologies for the delay, but its attached below!

Best,

Zak

[regisb]

@ZacharyM @jdunkerley79

Thank you so much! both of your solutions worked so I'll definitely keep these saved for future use! 

Zachary, thank you for taking the time for re-making, I had no idea you had to repeat the whole process. 

Have a great day!