Sum column dynamically depending on location

[ISUGraber]

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.  

[Hugo]

Could you provide a bit more detail on what your problem is? Perhaps a more worked example of data with 2 locations and what your expected output would be?

[danilang · Accepted solution]

Hi @ISUGraber 

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

Dan