Hi all,
I'm trying to figure out how to remove a duplicate value in the Liability column based on the what is in the Award ID and Country columns. See example below.
Award ID | Country | Liability | Apportioned Award |
1 | US | 1,000 | 7,500 |
1 | US | 1,000 | 2,500 |
1 | UK | 5,000 | 1,000 |
I thought perhaps this could be done with a Multi-Row Formula, but I'm getting an error.
IF [Row-1:Award ID] = [Award ID]
AND [Row-1:Country] = [Country]
AND [Row-1:Liability] = [Liability]
THEN [Liability] = "0"
Once run, i would expect the results to look like:
Award ID | Country | Liability | Appoortioned Award |
1 | US | 1,000 | 7,500 |
1 | US | 0 | 2,500 |
1 | UK | 5,000 | 1,000 |
Any suggestions?
Thank you.
Solved! Go to Solution.
Finish the expression with ENDIF.
And you may already have done this, but as a helpful hint, I would put a Sort tool right before the Multi-Row Formula tool to ensure that your groupings are consistent.
@jlinkkpmg I agree with @RodL. I've put his fix into a little workflow. By the way I personally prefer the IIF formula because I never have to type then/else/elseif/endif. In your case:
IIF([Row-1:Award ID] = [Award ID] AND [Row-1:Country] = [Country] AND [Row-1:Liability] = [Liability] ,[Liability] = "0" ,[Liability])
I also added another way to do it using a unique tool. I find this tool very powerful. Just my 2 cents
Thanks to both for the quick replies.
This was very helpful! I've been researching a similar issue and needed to remove duplicates if the Date Difference between duplicate record is >= 30 days.
After sorting by Date (Ascending), I applied the Multi-Field Formula tool and included the following statement:
IF [Row-1:ID] = [ID]
AND [Row-1:TYPE] = [TYPE]
THEN DateTimeDiff([DATE],[Row-1:DATE],"days")
Else Null()
Endif
This data can now be filtered accordingly.