Hi,

We need to generate an incrementing sequence number based on the status in the source records. Say, I have a series of 7 records and for the records with Status = 2, I would like to generate an incrementing series of sequence_key. Example:

Record	Status	Sequence_Key
1	1
2	2	1
3	5
4	2	2
5	1
6	2	3
7	2	4

I would also like to do this is one pass and not go back through in DM to update the records. Basically, is there a way in DM to get the previous not missing sequence_key and increment by 1.

Thanks!

This message has been edited. Last edited by: JL,

DM, do you mean Dialog Manager ?


For TABLE FILE

Use

TABLE FILE CAR
PRINT COMPUTE
      SEAT_2_SEQ/I9 = IF SEATS EQ 2 THEN LAST SEAT_2_SEQ + 1 ELSE LAST SEAT_2_SEQ ;

BY MODEL
BY SEATS
END

Hi Waz,

Actually, I meant the data migrator. I have changed my title to be more precise.

Thanks!

You can do the same thing in Data Management Console but then as a insert column in the SQL box. Then just use the example of Waz:

IF SEATS EQ 2 THEN LAST SEAT_2_SEQ + 1 ELSE LAST SEAT_2_SEQ

Thanks for the suggestion!

The expression:
SEATS = IF SEATS EQ 2 THEN LAST SEAT_2_SEQ + 1 ELSE LAST SEAT_2_SEQ
gave the following results:

Record	Status	Sequence_Key
1	1	0
2	2	1
3	5	1
4	2	2
5	1	2
6	2	3
7	2	4

So we created a temporary file to hold the results and used another expression:
SEATS_2_SEQ_NEW = IF SEAT_2_SEQ NE LAST SEAT_2_SEQ THEN SEAT_2_SEQ ELSE MISSING

Thanks for the help!