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

identifying the missing values from a sequence of numbers.

shankar12
8 - 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

5 REPLIES 5
MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
shankar12
8 - 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.

 

 

ThizViz
11 - 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
Ganbatte
5 - Atom

Hello,

 

Can anyone please help me with more detail for the workflow? I tried to follow the solution, but it looked like I missed something.

 

Thank you very much for your help.

pgindi002
5 - Atom

This was very helpful for me, thank you!

Labels