Identify missing numbers in a source column

kumarjit · Post by **kumarjit** » Fri May 01, 2015 11:58 pm

I have a source column with integer sequence, but the numbers are discontinuous.
Like 2, 4, 5, 6, 8, 25...... and so on.
There is no record count restriction, and I need only those numbers which are missing in the aource sequence as output.
like 1, 3, 7, 9, 10, 11, 12......

Please help.

Regards.

ray.wurlod · Post by **ray.wurlod** » Sat May 02, 2015 12:15 am

You will need sorted data, and you will need to operate in Sequential mode.

Use a Transformer stage. Use stage variables to "remember" the value from the previous row. Use a loop to traverse the interval between that and the current row's value, sending values less than the current row's value to the collection of missed numbers.

You could do the same in a BuildOp if you wanted to code the C++ yourself.

kumarjit · Post by **kumarjit** » Sat May 02, 2015 3:31 am

Thanks for the help Ray, but since I'm not a premium member, I'm not able to view your message fully.
But I've also tried using the same approach, but I'm not able figure out how to loop through the skipped numbers and retrieve their values.

Regards.

qt_ky · Post by **qt_ky** » Sun May 03, 2015 7:44 am

Consider joining as a premium member. It costs less than one restaurant lunch per month.

ray.wurlod · Post by **ray.wurlod** » Sun May 03, 2015 5:07 pm

Another approach might be to join/lookup with a complete list of numbers, which you could generate within the job. The beginning and end numbers could be provided as job parameters.

kumarjit · Post by **kumarjit** » Thu May 07, 2015 11:45 pm

qt_ky wrote:Consider joining as a premium member. It costs less than one restaurant lunch per month.