Hello everyone!
I have a SQL Query I want to create in Alteryx. This query goes through an Invoicing table and sums up different charges depending on the location that is associated with it. So each location can have different types for each column. Below is an example:
| Location | FreightCharge | InvoiceCharge |
| A | FRT | IVC |
| B | FRT, CHG | IVC, DTL |
This would go into a table like this for location A:
Select Location
, InvoiceNumber
, Case WHEN InvoiceType IN('FRT') THEN InvoiceAmount Else 0 End as FreightCharge
, Case WHEN InvoiceType IN('IVC','DTL') then InvoiceAmount Else 0 End as InvoiceCharge
From Invoicing
Where InvoiceDate = Today
This worked fine when I only had one location to worry about. Now I will potentially have hundreds. I created a Locations table that has the types in it (the table above). But I'm not convinced it's the right way to do this.
ultimately I would either want a SQL Statement (alteryx) that can do this from the ground up or (more likely) a workflow I can use to loop through locations which links to my reference table and automatically tallies this data.
Any ideas?
However, it would be nice if I could run this for each location, appending it to an invoice table.
