[Solved] Data Migrator - Generate a sequence number
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,
Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
June 08, 2009, 06:10 PM
Waz
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
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
June 09, 2009, 10:13 AM
JL
Hi Waz,
Actually, I meant the data migrator. I have changed my title to be more precise.
Thanks!
Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
June 09, 2009, 03:00 PM
Frans
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
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!
Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.