Add two lines together

[Dath1rd]

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 Date	Invoice Number	Ship Date	Tracking ID	Reference ID	Billed weight	Zone	Ship to State	Item shipped	Qty item shipped	Item Shipped 2	Qty item shipped 2	Additional Handling	Additional Handling - Length+Girth	Addl. Handling weight	Address Correction Ground	Commercial Adjustment	Delivery Area Surcharge	Delivery Area Surcharge - Extended	Delivery Area Surcharge - Extended Adjustment	Delivery Area Surcharge Adjustment	Demand Surcharge-Addl Handling	Demand Surcharge-Com	Fuel Surcharge	Fuel Surcharge Adjustment	Ground Commercial	Ground Commercial Third Party	Ground Commercial UPS Delivery Intercept	Ground Residential	Ground Return to Sender	Ground Undeliverable Return	Not Previously Billed Additional Handling - Length+Girth	Not Previously Billed Delivery Area Surcharge - Extended	Not Previously Billed Demand Surcharge-Addl Handling	Not Previously Billed Fuel Surcharge	Not Previously Billed Ground Commercial	Not Previously Billed Ground Residential	Not Previously Billed Missing PLD Fee	Not Previously Billed Residential Surcharge	Peak/Demand Surcharge-Addl Handling	Remote Area Surcharge	Reroute - Web Request	Residential Adjustment	Residential Surcharge	Residential Surcharge Adjustment	Return To Sender - Phone Request	Return To Sender - Web Request	Returns Additional Handling	Returns Additional Handling - Length+Girth	Returns Addl. Handling weight	Returns Demand Surcharge-Addl Handling	Returns Fuel Surcharge	Returns Ground	Returns Print Label	Returns UPS carbon neutral	Service Charge	Shipping Charge Correction Additional Handling	Shipping Charge Correction Additional Handling - Length+Girth	Shipping Charge Correction Addl. Handling longest side	Sum_Shipping_Charge_Correction_Addl__Handling_weight	Shipping Charge Correction Demand Surcharge-Addl Handling	Shipping Charge Correction Demand Surcharge-Large Package	Shipping Charge Correction Fuel Surcharge	Shipping Charge Correction Ground	Shipping Charge Correction Ground Undeliverable Return	Shipping Charge Correction Large Package Surcharge - Length + Girth	Shipping Charge Correction Large Package Surcharge Resi	Shipping Charge Correction Over Maximum Size	Shipping Charge Correction Peak/Demand Surcharge-Addl Handling	UPS carbon neutral	Weekly Adjustments	Worldwide Expedited	ZONE ADJUSTMENT Addl. Handling weight	ZONE ADJUSTMENT Fuel Surcharge	ZONE ADJUSTMENT Ground	Total
Final Result	8/26/2023	TestInvoice012343	8/22/2023	1ZTEST019092495329	TestREF123456	35	4	GA		0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	-7.6	8.2	0	0	0	0	0.18	0.61	0	0	0	0	0	0	0	0	0	0	0	1.39
	8/26/2023	TestInvoice012343	8/22/2023	1ZTEST019092495329	TESSTTT3549437E return	35	4	GA		0		0																																																				0.61												0.61
	8/26/2023	TestInvoice012343	8/22/2023	1ZTEST019092495329	TESSTTT3549437E return	0	4	GA		0		0																																													-7.6	8.2					0.18													0.78

[MichelleMitchellLutz]

Hi!

Can you please attach a copy of your workflow? Also, are you trying to combine the totals if the Tracking ID is the same?

[Dath1rd]

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.

[MichelleMitchellLutz]

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. 

 

[Dath1rd]

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.