Multi row / Multi Field check and Update

Question

Hello there,

I have the following problem to solve (really hard one), i have a list of row (> then 100) that have this kind of pattern:

N°OrderSub_orderMaterialDescriptionMaterial_2 Valute to withdraw_quote Material descriptionQty to be picked_quoteDate_withdrawal_quoteMaterial Doc.Date_withdrawalQty_withdrawnDelta Value_withdrawal 1Project1 2306394G11220AB004A268W725E01                     281.183,40 €Material a5402/05/2021490062613420/04/2021-252           10.414,20 €2Project2P1572067G11220AB003B268W725E01                     281.183,40 €Material a5411/08/2021  054                           -   €3Project1 2306394G11220AB004A268W725E01                     281.183,40 €Material a5402/05/2021490063633805/05/2021-531         275.976,30 €

Now, i need to update this table with the following logic:

1) sort all rows by ascending Date_withdrawal value (this should be a simple sort) then:

2) Check row 1 and compare it with all the sheet:

* Order
* Material_2

if you find a row that have both the same values (for example line 3)

* Valute to withdraw_quote >> Set to 0 the lowest row
* Qty to be picked_quote >> should be reduced by the qty that have been withdrawn (54-2=52)

the result should be something like:

N°OrderSub_orderMaterialDescriptionMaterial_2 Valute to withdraw_quote Material descriptionQty to be picked_quoteDate_withdrawal_quoteMaterial Doc.Date_withdrawalQty_withdrawnDelta Value_withdrawal 1Project1 2306394G11220AB004A268W725E01                     281.183,40 €Material a5402/05/2021490062613420/04/2021-252           10.414,20 €2Project2P1572067G11220AB003B268W725E01                     281.183,40 €Material a5411/08/2021  054                           -   €3Project1 2306394G11220AB004A268W725E01                     0€Material a5202/05/2021490063633805/05/2021-531      it will be updated later

this logic should work also with an hypothetical 4th row. in that case the system should use the same logic but it should consider the fact that 3 withdrawal have been made

can someone help me?

many thanks!

Kenda · Answer

Hi @Guglielmo91

It looks like when I copied your sample data over, there was a space at the beginning of the "Value to withdraw_quote" field name and that is why you aren't able to find it when you rename the field.

If you right click on the macro, you can open it to see what's going on inside. You can either change the field name that the macro uses in there or you can change the name when you rename it in the Select tool.

Hope that helps!

mceleavey · Answer

Hi @Guglielmo91 ,

I've built this using a multi-row formula and sampling the last of the group as specified.

During the sampling I've used a join and taken the left to determine if the last in a group is the same as the first (there's only one per group) and these will be ignored:

I hope this helps,

M.

mceleavey_solution.yxmd

Guglielmo91 · Answer

Hi Kenda, Thanks you so much for your help!! I had no idea of this tool, I've tried to incorporate your code inside of mine but I'm experiencing some problems. These are more certainly linked to the fact that in my data set i have columns that were not present in the example that i posted and the name of them have been changed for "translation" reasons.

I have tried to rename them accordingly to your program but it not seems to work. This is how i have renamed them:

an this is the issue that i'm facing:

I don't want to take advantage of your kindness but I can't find the issue here. Can you help me? maybe i can send you the entire list of data and you can check if there is anything wrong.

thanks in advance

Guglielmo