Data Transfomation

[sreejithmp]

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

[Peachyco · Accepted solution]

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:

 

[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.

[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.