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

[ewang]

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. 

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

[RolandSchubert · Accepted solution]

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

[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

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