workflow to delete rows based on values

[barkat]

Hello, 

I have been trying to get this particular output form my data,have tried to use the multi row but did not get very far with it. Hoping someone can advice on a better approach or logic? 

 for the same master_id , when the field 'type' is a 'correction' and the pay field has not changed then I want to delete the second row, as it has not changed the transaction. However I want to populate the column date2 with the correction date. And when there is no change in the two rows leave it as it is, Please example below. 

what my data looks like:

pay	master_id	type	date	date2
200	12	new	5/11/2020  12:20
200	12	correction	5/11/2020 12:24
100	11	new	8/11/2020 10:24
400	11	correction	8/11/2020 10:45

what i want to achieve: 

pay	master_id	type	date	date2
200	12	new	5/11/2020  12:20	5/11/2020 12:24
100	11	new	8/11/2020 10:24
400	11	correction	8/11/2020 10:45

[AngelosPachis]

Hi @barkat ,

Maybe there is a way to do this without a multi-row formula tool, but you need a slightly different logic.

Since you want to "delete" rows if you have more than one records per pay, master id combination, I used a summarize tool to count how many records you have for each pay, master id combination.

Then I brought the data back together with a join tool, and then I used a filter tool to "delete" a column if the count is larger than 1 and the type is correction. However, we don't actually delete it, because we need to bring that additional date back, in the [date2] field. That you can do easily with a Join tool, and then you can finally use a Union tool to bring your table in the original structure.

Hope that helps, let me know if that worked for you.

Cheers,

Angelos

[Qiu · Accepted solution]

@barkat 
Here is a multirow version.