Unmerging and copying to be able to transpose

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!

Portfolio flows - Alteryx problem.xlsx

Datasets

Help

Time Series

Accepted answers

jdunkerley79

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

Reshape Excel.yxmd

ZacharyM

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_2018.3.yxzp

All comments

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!