community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

identifying the missing values from a sequence of numbers.

Asteroid

HI 

 

I have a data set related to orders , need to identify the missing order numbers,only trouble  is my input have some data with repeated numbers

 

input 

order Number
15234011
15234012
15234013
15234013
15234015
15234016
15234017
15234019
15234020
15234025
15234026
15234027
15234031
15234031
15234032
15234033
15234034

 

Output

 

order Number
15234011
15234012
15234013
15234013
15234014
15234015
15234016
15234017
15234019
15234020
15234021
15234022
15234022
15234023
15234024
15234025
15234026
15234027
15234028
15234029
15234030
15234031
15234031
15234032
15234033
15234034

 

Thanks

Alteryx Certified Partner
Alteryx Certified Partner
Terse response:

Summarize data get min and max order numbers.

Feed output to generate records and configure start as min and formula to rownum <= max.

You can join this data to the original data.
Use the left input for the original data and the output from the right anchor are the missing orders.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

HI Marquee,

 

Thanks a lot for the help . I got that.

 

Just a question/suggestion for me how you got the thought of this way identifying the solution, using the particular tool.

 

New to alteryx world, your suggestion/inputs much appreciated.

 

Thanks.

 

 

Highlighted
Bolide

I'll take a stab at this, but Mark can certainly give his perspective.

 

Any time you have data where you know that numbers need to be sequential and you know that numbers are missing, that's an indicator you'll need the generate records tool. This tool is often used to logically "fill in the blanks".

 

To determine which blanks need to be filled in, you know you're looking for the numbers in between a given order number (MIN) and another (MAX).

 

Does that help?

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
Labels