Understanding Multi-Field Formula

mustufa2019

Hi, I wrote an expression for a Multi-Field Formula a while back and now I'm having difficulty recalling what the expression is doing. I have attached two screenshots below, if someone can help guide me I would really appreciate it:

I just can't recall what I did here and what the output would be of these expressions 😞

Workflow

Tips and Tricks

Transformation

Preparation

Expression

Data Investigation

Accepted answers

Blake

Hey @mustufa2019

It looks like you're doing a check for duplicate records based on 4 fields (Invoice ID clean, Invoice ID, Supplier ID, and Invoice date). It appears the duplicates will be marked as 1 and the first occurrence as 0. This will only mark duplicates that appear consecutively in your data. Not sure why you have the second multi-field formula.

You could probably accomplish this a bit easier by using a Unique tool and marking the 4 fields that are of interest. The current tool you have set up won't catch something like the screenshot below where there is a record in between the duplicates.

Thanks, let me know if this helps.

All comments

Blake

Hey @mustufa2019

Thanks, let me know if this helps.

estherb47

Hi @mustufa2019

This is a Multi-Row formula tool. Think of that as a formula tool that will look at previous or following rows to do an analysis. Looks like the first formula is looking to see if the fields in the current row match the fields in the row above, and marking them as duplicates if they do.

I'm unsure of the reason that the second one is needed. It seems to be looking if the previous row were marked duplicate. If it is, then mark the current one as duplicate too, otherwise copy the zero. That could add on rows that aren't duplicates though, which is my struggle to understand.

A Multi-field formula tool works across a single row in your data. It lets you apply the same transformations across multiple columns, instead of needing to write the same formula over and over again in a Formula tool.

Let me know if that helps.

Cheers!

Esther

fmvizcaino

Hi @mustufa2019 ,

For the first image, you are verifying if the invoice ID, Supplier ID and Invoice date are equal to the row before.

The row-1 means that you are looking to the row right above and row+1 the right below. So if you are in line 2, the row-1 is 1 and row+1 is 3.

The second image is flagging the first duplicated line as well.

Invoice ID	Supplier ID	Invoice Date	Is this row duplicated? First multi-row	Is this row duplicated? Second multi-row
A	A	A	0	0
B	B	B	0	1
B	B	B	1	1
C	C	C	0	0
D	D	D	0	1
D	D	D	1	1

Best,

Fernando Vizcaino

Blake

I looked at the formula again and realized that you are looking for Invoice ID to NOT match the row above. You are marking when supplier ID, invoice date, and invoice ID clean match but Invoice ID does not. It'd be interesting to know where the fields originated and why you expect the those 3 fields to match and invoice ID not to match. Either way, good luck!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703