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 calculate numbers from one file to another without the same reference

Floris77
6 - Meteoroid

Hi all,

I'm again struggling with something which seems to look easy to solve, but I can't fix the puzzle myself. Hope someone here can help me out!

 

I have 2 different input files (see attached file as an example)

 

The first one shows the share of the total per agegroup per track.

The second one shows the share of the total per agegroup per playlist.

 

Now I want to calculate the variance per agegroup between each track and the various playlist.

 

So, if for track A, 20% of the audience is from the age group 10-30, and for playlists 1 it's 10%, then I want to see a 200% (20/10) as a score for that track for that specific playlist and so on.

 

How can I do this.. I can't join the files first, right and union feels weird..  

 

Find attached an excel file which hopefully clarifies the issue a bit better.

 

thanks again!

Best,

Floris

2 REPLIES 2
patrick_digan
17 - Castor
17 - Castor

@Floris77 Thanks for including the great excel file! I would use a combo of crosstabs and transposes to reorient your data.

 

Hope that helps!

bsharbo
11 - Bolide

I have attached a workflow that should solve your problem. You will have to re-order the columns to the order you want on the output, but the idea is sound in theory (you can dynamically re-order columns if needed).

 

Note: the key is to pivot your data into a typical table format so that you can join on the column values (10-30) as that is effectively what you are asking to join on. 

 

Let me know if you have any questions. 

Labels