Data Transfomation

Hi Data folks

Please find the attached data i have 3 tables and based on common column i need to combine the results and find the sum is the request. I have tried join multiple but couldnt find a way to summary like below. Please help.

Ask; based on currency.a_code,b_code i need to fetch values from 3 tables and find row sum of c_value+d_vale+E_value

image.jpg

tables.xlsx

Transformation

Optimization

Accepted answers

Peachyco

After joining the three tables together, you can use a Formula Tool to calculate [Sum].

But that approach is not dynamically scalable, in the sense that you would need to keep modifying the Formula Tool for each column you want to add to the [Sum]. We can make it a bit more scalable if we use the Transpose Tool and the Summarize Tool like so:

Alteryx screenshot.png

For sreejithmp - Data Transformation.yxmd

All comments

Peachyco

After joining the three tables together, you can use a Formula Tool to calculate [Sum].

Alteryx screenshot.png

For sreejithmp - Data Transformation.yxmd

ed_hayter

I've started with the most basic of cases where this doesnt need any automated recognition.

we use select records to take the first part of the sheet and the summary table into seperate branches for validation purposes.

In this simple case the order of the the currencies are consistent so i just corrected data types and replaced null with 0 in numeric columns. Then did a row level sum to get the same results as the summary table.

If the rows are not in the correct order. I could use a select tool to split the table into three branches for the three tables and then join on the currency indicator fields A and B before doing the steps listed above.

Calculation.yxzp

FrederikE

Hey @sreejithmp,

I think this is what you would want:

For the sum you can just add the columns with a formula tool.

sreejithmp

Thank you @Peachyco awesome

sreejithmp

Thank you @ed_hayter for giving me the solution by considering the cases i also missed.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335