I have an optimized QVD that acts as my master data file for historical data. It has a key field in it that is manually set by RecNo(); this assures that every row is unique. I am trying to run an update every week, that will concatenate new data to my master QVD and pick up the ID field where the master left off. So as an example:
ID Name Comment
1 Bob Good
2 Sally Nice
3 Joe Bad
4 Sarah Terrible
.... .... ....
Now let's say my master QVD file has 100 records, and the last ID is 100 (created by RecNo() function)
Now I'm running an update for the week, and I wanna concatenate 10 new records.
ID Name Comment
101 Steve Good
102 Mike Nice
103 Tony Bad
104 Kate Terrible
... ... ...
110 Bob Great
What I've tried doing already was to set a variable like varLastID = NoOfRows('MasterQVD')
then as my ID for the week update I would have
varLastID + RecNo() AS ID
and finally, concatenate the two tables... For some reason, I am getting duplicate ID's still. Is there an easier way to insure that I have unique keys if I am manually creating them? I don't really want to join two fields and have that as my key, that tends to get messy. I just want a simple counter that will pick up where my master table left off. Would preceding loads affect the RecNo() function? Even if I had it at the top LOAD?
Hi Paulo, I'm guessing you concatenate both qvds, historical and new data. In this case, use the funtion rowno(), it doesn't reset between load statements and does not count the records excluded by a where clause. I think for your task in particular, the function rowno() is safer.