Expecting the Value in Column

[Rhaldhar]

Hi All,

I have below blue table after calculating the difference coming (.02) in green box and expecting it automatically adjust with h column as basis of first invoice of every invoice number and give the figure in column J as result needed and different should be zero as showing in yellow box. 

Please refer to the attached excel file as well. Please advise the WF

Thanks,

Rajeev

[Prometheus]

@Rhaldhar I started by replacing the nulls in the unnamed field in your data and Alteryx named the field Field5. If you try to calculate using nulls, you won't get what you need so you have to replace them with zeroes. I then created the field Result Needed by adding Total Amount  + Field5. I used the Summarize tool to group by FX and to create totals and include in the field names the cell number on your worksheet, such as Sum_Amount (F12), Sum_ Total Amount (H12), and Total Result Needed (J12). Lastly, I used the Formula tool to perform the calculations.

 

 

 

 

[caltang]

Hi Rajeev, 

My advice is to work with the raw data that you have, input that into Alteryx as the main dataset. 

Then, have a mapping file that has the adjustments tied to their respective invoice.

Use a Join tool to combine base on Invoice, then use the Formula Tool to adjust. Then export whatever you want from there with a Select Tool + Output Data Tool.

Hope this advice helps.

[Rhaldhar]

Hi,

Colum "I" is a example of the +- figure it will not be available in input data on basis of green box difference (.2) it to be split .01 and .01 if the difference is .04 then to be split in 4 row .01, .01,.01, .01. so, please have a look again on the query. Thanks for looking.

[Prometheus]

@Rhaldhar I don't understand what you're asking because the layout of your data is confusing. Which part of your worksheet is the raw data? What are you trying to accomplish?

[Rhaldhar]

@Prometheus Hi column E to H is raw data and circled one is calculations and dark Green (column H) is difference which need to be adjusted into H column, example highlighted in I column and result need as coming in J column. so that J column total and circled calculations total should same and different should be zero. 

I hope this will help you to understand.

Thanks,

Rajeev