How to do math in one table based on the values in another table

Hi,

I have lookup table A which contains different prices for items A and B.

Table A:

I have another table B which has the province, city, A and B. Columns A and B in this table are having the same names as the items name A and B in table A.

Table B:

I want to get the values by multiple the quality in Table B with the prices in Table A, and come out with the total, so for each record in table B will have 3 records at the end as there are 3 different sets of price for the items.

So the expected result will be:

Does anyone has any idea how to do this in Alteryx? Thank you in advance and really appreciate any insights.

Best Practices

Accepted answers

RolandSchubert

Hi @ewang ,

there are different options to do the calculation. A very simple method would be to use the Append Fields tool to add the price columns to the quantities and do the calculation using a Formula tool, a more flexible approach would use Transpose and Join tool. I've attached a sample workflow showing both approaches.

Let me know if it works for you.

Best,

Roland

Multiplication.yxmd

All comments

grazitti_sapna

Hi @ewang , you can use append tool , formula tool and a select tool to achieve the desired output. Please refer to the screenshot and workflow.

Calculation.yxmd

RolandSchubert

Hi @ewang ,

Let me know if it works for you.

Best,

Roland

Multiplication.yxmd

bhrmitra

Hi ewang,

You can use a append to to solve this. Append tool works as a cross join.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra

Solution.yxmd

ewang

This works. Thank you very much, for some reason I thought Append too like a union tool, and didn't realize it works like a cross join. It will help a lot as I will keep it in mind now.

ewang

Thank you very much, it does work for the question I have now. And yes, thanks for pointing it out that Append tool works like cross join, I for some reason got that slip off my mind. Thanks a lot.

ewang

@RolandSchubert wrote:
Hi @ewang ,
there are different options to do the calculation. A very simple method would be to use the Append Fields tool to add the price columns to the quantities and do the calculation using a Formula tool, a more flexible approach would use Transpose and Join tool. I've attached a sample workflow showing both approaches.
Let me know if it works for you.
Best,
Roland

Thanks a lot Roland,

I like the transpose and cross-tab solution here as it allows me to do further grouping here by the record id. Really appreciate all the answers here, I have learned something new again. 🙂

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703