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

Missing Numbers in a Sequence

Janet_Samuels
7 - Meteor

I have a list of Invoice ID Numbers.  They should each be unique AND in sequence (so there should be no gaps in numbers).

 

I can easily check for duplicate Invoice ID Numbers.  However, I can't figure out how to determine if any Invoice ID Numbers are missing in the sequence.  In Excel, I would order the invoices by the Invoice ID Number.  Then I could check that the current row's Invoice ID number matches the previous number plus one.  Any that didn't match would be flagged.

 

I've tried using the "Multi-Row Formula" Tool where my Invoice ID Number is a "Double". I created a new field with the following expression:

[Row-1:InvoiceID]+1

 

I would have thought that this would take the previous row (the "Row-1") of the InvoiceID Field and add one to it. However, I get "1" as the data for each record in the new field

 

I've thought about using the summarize tool to get the Min and the count of the Invoice ID and then create a table from from the Minimum Invoice ID, and adding 1 for the COUNT number of times...but I can't figure out how to do that.

 

There's got to be an easy way to do this that I'm missing..........

7 REPLIES 7
Felipe_Ribeir0
16 - Nebula

Hi @Janet_Samuels 

 

I believe that the multi row formula is the way to go.

 

 

Felipe_Ribeir0_0-1673628855171.png

 

Janet_Samuels
7 - Meteor

Thank you!  That worked.  My issue was that I had clicked the "Group by" box...and thus I was getting inaccurate results.  Once I mimicked yours (WITHOUT checking that box, it worked like a charm!).

 

Janet

RobertOdera
13 - Pulsar

Hi, @Janet_Samuels 

 

Here you go, and this will also identify the missing Invoice ID.

Please mark this as an acceptable solution if this works for you.

Cheers!

 

MissingNumbersInSequence.PNG

Janet_Samuels
7 - Meteor

Thanks Robert...that solution works as well.  It is a bit longer of a workflow but it will identify the exact ID numbers that are missing (and if I have multiple missing ID numbers in the sequence (e.g. if 999750 and 999751 are both missing)

RobertOdera
13 - Pulsar

You're most welcome, @Janet_Samuels.

Usually, after you flag an error, it is helpful to know (exactly) what you need (what is missing) 👌.

ctvalderama
5 - Atom

Hi! Is it possible to configure this workflow where the column of invoice id is composed of alphanumeric characters? Thank you!

aatalai
12 - Quasar

@Janet_Samuels you could also possibly use the imputation tool

Labels