Tableau - Take row-wise data and pivot it to have an output table based on separate table of product hierarchies

Question

I have sales data with a unique customer ID on each row and their corresponding sales by Product on columns. I have a separate table for product hierarchy, which has a mapping of all products (column names) and the corresponding product hierarchy. I want to somehow pivot the original sales table within Tableau to return a table with Hierarchy --> Product --> some discount based on a Tableau calculated field. Is this possible with Tableau?

This is an example of the sales table:

This is the product hierarchy cross-match. Prod 3 and 4 belong to the same hierarchy:

This is the final table I want to return:

Thank you for the help!