This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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:
This would go into a table like this for location A:
, 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
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.
However, it would be nice if I could run this for each location, appending it to an invoice table.
The method of creating the Charge Mapping table is a perfectly acceptable way to go about it.
The top an bottom branches are functionally identical, with the top handling the freight charges and the bottom handling the InvoiceCharges. On the top, start by selecting the Location and ChargeType columns. Split the charge types to rows join to the Invoice table on Location and ChargeType. For the Invoices that do match, create a new FreightCharge column with the Invoice Amount. For those that don't, create a FreightCharge column with 0. Union these two streams. Repeat the process along the bottom for the InvoiceCharge and finally join the results on Location and Invoice Number, resulting in
From here you can apply any summarization operation that you need