Add two lines together

Question

Hello Everyone. i am a complete novice with Alteryx. I apologize if this has been said countless times,

I have the following scenerio to solve.

I compiled all generated invoices from our shipping company. I was able to achieve my final result, however stuck on adding the same tracking IDs with matching columns together. see the attached.

Steps to final result.

1.AutoField spreadsheet

2.Data Cleansed

3.Selected the rows i wanted to work with and renamed them

4. text to column to split certain columns and get ITEMS and qty shipped

5. selected coulmns i needed

6. sorted by invoice number

7. used formula tool IF Contains([Shipping Charge Descriptions],"Billing Adjustment") THEN "Weekly Adjustment" ELSE [Shipping Charge Descriptions] ENDIF

8. Crosstab(sum)- used "weekly Adjustments" as columns and used "discounted price" as value columns.

9. Sorted

10.selected

exported.

Note that. This can be solved by me unselecting "billed weight" in the Crosstab Sum, but i wont be able to see the total billed weight that i need for other analysis.

I have tried doing a a filter and using Billed weight != 0 add a summarize tool for the section that is not true. use a join tool and join them by billed weight and tracking ID, add a formula tool that replaces any 0 with the added or sum_billed weight.

Invoice DateInvoice NumberShip DateTracking IDReference IDBilled weightZoneShip to StateItem shippedQty item shippedItem Shipped 2Qty item shipped 2Additional HandlingAdditional Handling - Length+GirthAddl. Handling weightAddress Correction GroundCommercial AdjustmentDelivery Area SurchargeDelivery Area Surcharge - ExtendedDelivery Area Surcharge - Extended AdjustmentDelivery Area Surcharge AdjustmentDemand Surcharge-Addl HandlingDemand Surcharge-ComFuel SurchargeFuel Surcharge AdjustmentGround CommercialGround Commercial Third PartyGround Commercial UPS Delivery InterceptGround ResidentialGround Return to SenderGround Undeliverable ReturnNot Previously Billed Additional Handling - Length+GirthNot Previously Billed Delivery Area Surcharge - ExtendedNot Previously Billed Demand Surcharge-Addl HandlingNot Previously Billed Fuel SurchargeNot Previously Billed Ground CommercialNot Previously Billed Ground ResidentialNot Previously Billed Missing PLD FeeNot Previously Billed Residential SurchargePeak/Demand Surcharge-Addl HandlingRemote Area SurchargeReroute - Web RequestResidential AdjustmentResidential SurchargeResidential Surcharge AdjustmentReturn To Sender - Phone RequestReturn To Sender - Web RequestReturns Additional HandlingReturns Additional Handling - Length+GirthReturns Addl. Handling weightReturns Demand Surcharge-Addl HandlingReturns Fuel SurchargeReturns GroundReturns Print LabelReturns UPS carbon neutralService ChargeShipping Charge Correction Additional HandlingShipping Charge Correction Additional Handling - Length+GirthShipping Charge Correction Addl. Handling longest sideSum_Shipping_Charge_Correction_Addl__Handling_weightShipping Charge Correction Demand Surcharge-Addl HandlingShipping Charge Correction Demand Surcharge-Large PackageShipping Charge Correction Fuel SurchargeShipping Charge Correction GroundShipping Charge Correction Ground Undeliverable ReturnShipping Charge Correction Large Package Surcharge - Length + GirthShipping Charge Correction Large Package Surcharge ResiShipping Charge Correction Over Maximum SizeShipping Charge Correction Peak/Demand Surcharge-Addl HandlingUPS carbon neutralWeekly AdjustmentsWorldwide ExpeditedZONE ADJUSTMENT Addl. Handling weightZONE ADJUSTMENT Fuel SurchargeZONE ADJUSTMENT GroundTotalFinal Result8/26/2023TestInvoice0123438/22/20231ZTEST019092495329TestREF123456354GA 0 000000000000000000000000000000000000000000000-7.68.200000.180.61000000000001.39 8/26/2023TestInvoice0123438/22/20231ZTEST019092495329TESSTTT3549437E return354GA 0 0                                                   0.61           0.61 8/26/2023TestInvoice0123438/22/20231ZTEST019092495329TESSTTT3549437E return04GA 0 0                                            -7.68.2    0.18            0.78

Test3.xlsx

New Workflow2.yxmd

Input data.xlsx

Dath1rd · Answer

Hi Michelle,

Thank you for your input. The total this said invoice should be $5,027.79. the duplicates are not adding up. Every tracking that has a 0 in the billed weight also has different charges and so do the ones that have a value in the Billed weight column. the only reason the tracking numbers should not add up to together in charges is because they are from a different state. I am thinking that this should be done before i do a cross tab which does like a vlookup from the tracking column and returns the value of the weight. not sure if that makes sense.

MichelleMitchellLutz · Answer

Hi!

I wasn't sure if you wanted to drop the duplicate tracking ID/States, I dropped them with the Unique tool. Please see below and let me know if this solves your question, or you if you need additional assistance.

Add two lines together_MML Solution.yxmd

Dath1rd · Answer

Hi Michelle,

I've added the workflow and sample data and yes that is correct. tracking numbes should add up unless its a different state. The issue i currently have is that it does not add up because the weights are different in the Billed weight column. they either have a weight or 0. these should add together unless the state is different.