sequence number

[adams06]

Hi All,

How to check if,The numbers are missing a sequential numbers between 1 and 10

EX: if 1 2 3 4 6 7 8 10--> skip record(reason is they are not in sequence)
if 1 2 3 4 5 6 7 8 9 10---> accept record.


thanks in advance

[DSguru2B]

This has been covered a lot here. Check out this and this post.
Also make sure you sort your incoming data.

[ray.wurlod]

Are these numbers in a single field (and, if so, how are they delimited) in the same record, in separate fields in the same record, or in the same field in multiple records (and, if so, how is the input sorted)?

[adams06]

Hi DSguru,

I have 8 fields in my source table.

For each book i have different version.(book and versions are my field names)

for example

book version
1-100 0
1-100 2
1-100 4
1-200 0
1-200 1
1-100 2

i need to check the version number for a book are missing a sequential version
between min and max.


How can i implement this.


thanks in advance.

This has been covered a lot here. Check out this and this post.
Also make sure you sort your incoming data.

[DSguru2B]

So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence.

[adams06]

Hi DSguru,

If there are out of sequence then i need to bypass them( i mean i need to throw of the entire 1-100)

if it is in sequence i need to accept( i.e like 1-200)

Thanks in advance.

So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence ?

[DSguru2B]

Ok for that you cannot do it in a single transformer. You need to determine what books are in order, flag them and then in the second transformer only let them pass through.
Use the conditional checks in the first transformer to build a hashed file with only one column 'book'.
So basically in the first transformer you will have the following stage variables.

Code: Select all

VerChk: if in.book = prevBook or @INROWNUM = 1 then @TRUE else @FALSE 
SeqChk: if VerChk = @TRUE AND  in.Version = prevVer + 1 then @TRUE else @FALSE 
prevBook: in.book 
prevVer : in.Version 

In the constraint of the output link put SeqChk = @TRUE. This link will go to a hashed file with only one column 'Book'. Define that as a key.
Now that your hashed file is ready. Let you input come in from the source table, do a lookup on this hashed file (key will be book only) and in the constraint of the output link you should have

Code: Select all

NOT(lkup.NOTFOUND)

where lkup will be the name of your reference link to the hashed file.

This solution will work because the hashed file has only those books which are in sequence.
Have a great weekend

[adams06]

Hi DSguru,

how to get prevBook and preversion.


Thanks in advance

So let me make sure i got this straight!
for 1-100 you are missing version 1 and 3 and for book 1-200 you are not missing any version. So you have to output
1-100 0
1-100 3
for the data sample that you gave. Correct?
Or you just have to raise a red flag if they are out of sequence.

[sud]

Abracadabra hocus focus

Code: Select all

select * from books where book in (select t.book countversion from books t 
where t.version between #start_version# and #end_version#
group by t.book having count(distinct t.version) = #end_version# - #start_version# + 1)

assuming that table name is books and that the column version is an integer.
:D

[DSguru2B]

how to get prevBook and preversion.

i have shown it in my stage variables.
when i say in.book, that means your column 'book' and 'in' is for input, so in other words, your input column 'book', similarly for your version.
From your input just drag and drop the book and version to these two stage variables.

[DSguru2B]

Abracadabra hocus focus

Code: Select all

select * from books where book in (select t.book countversion from books t 
where t.version between #start_version# and #end_version#
group by t.book having count(distinct t.version) = #end_version# - #start_version# + 1)

assuming that table name is books and that the column version is an integer.
:D

Nice. I like that. How is he going to pass the start_version and end_version to the sql

[sud]

Those will be job parameters isnt it. :D

[DSguru2B]

Yes it will be. Just pulling your leg sud