Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Sum column dynamically depending on location

Highlighted
Asteroid

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:

 

LocationFreightChargeInvoiceCharge
AFRTIVC
BFRT, CHGIVC, 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.  

Highlighted
Asteroid

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?

Highlighted
Castor
Castor

Hi @ISUGraber 

 

The method of creating the Charge Mapping table is a perfectly acceptable way to go about it.  

 

W.png

 

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

 

R.png  

 

From here you can apply any summarization operation that you need

 

Dan  

Labels