Multi row / Multi Field check and Update

[Guglielmo91]

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°

Order

Sub_order

Material

Description

Material_2

Valute to withdraw_quote

Material description

Qty to be picked_quote

Date_withdrawal_quote

Material Doc.

Date_withdrawal

Qty_withdrawn

Delta

Value_withdrawal

1

Project1

2306394

G11220AB004

A

268W725E01

281.183,40 €

Material a

54

02/05/2021

4900626134

20/04/2021

-2

52

10.414,20 €

2

Project2

P1572067

G11220AB003

B

268W725E01

281.183,40 €

Material a

54

11/08/2021

0

54

-   €

3

Project1

2306394

G11220AB004

A

268W725E01

281.183,40 €

Material a

54

02/05/2021

4900636338

05/05/2021

-53

1

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°

Order

Sub_order

Material

Description

Material_2

Valute to withdraw_quote

Material description

Qty to be picked_quote

Date_withdrawal_quote

Material Doc.

Date_withdrawal

Qty_withdrawn

Delta

Value_withdrawal

1

Project1

2306394

G11220AB004

A

268W725E01

281.183,40 €

Material a

54

02/05/2021

4900626134

20/04/2021

-2

52

10.414,20 €

2

Project2

P1572067

G11220AB003

B

268W725E01

281.183,40 €

Material a

54

11/08/2021

0

54

-   €

3

Project1

2306394

G11220AB004

A

268W725E01

0€

Material a

52

02/05/2021

4900636338

05/05/2021

-53

1

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]

Hello @Guglielmo91 

Sounds like you need an iterative macro here! I've created a workflow with your sample data to get you started. Try it out with your full set of data. 

If you need additional help with iterative macros, check out these resources:

• https://community.alteryx.com/t5/Interactive-Lessons/Creating-an-Iterative-Macro/ta-p/657925
• https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Iterative-Macr...

[Guglielmo91]

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 

[mceleavey]

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.

[Kenda]

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!