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

Unmerging and copying to be able to transpose

regisb
7 - Meteor

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! 

image.png

 

7 REPLIES 7
ZacharyM
Alteryx Alumni (Retired)

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!

 

image.png

 

Best,
Zak

jdunkerley79
ACE Emeritus
ACE Emeritus

I attach my go:

 

2018-12-11_18-54-41.png

 

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
17 - Castor
17 - Castor

 

Nice work @ZacharyM and @jdunkerley79!

regisb
7 - Meteor

@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
7 - Meteor

@jdunkerley79

 

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

 

 

ZacharyM
Alteryx Alumni (Retired)

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
7 - Meteor

@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! 

Labels