Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Understanding Multi-Field Formula

mustufa2019
8 - Asteroid

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 😞 

 

mustufa2019_0-1589421013652.png

 

mustufa2019_1-1589421035306.png

 

4 REPLIES 4
Blake
12 - Quasar

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. 

 

Blake_0-1589422513379.png

 

 

 

Thanks, let me know if this helps. 

estherb47
15 - Aurora
15 - Aurora

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
17 - Castor
17 - Castor

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 IDSupplier IDInvoice DateIs this row duplicated?
First multi-row
Is this row duplicated?
Second multi-row
AAA00
BBB01
BBB11
CCC00
DDD01
DDD11

 

Best,

Fernando Vizcaino

Blake
12 - Quasar

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! 

Labels