1 Reply Latest reply: Sep 23, 2010 4:39 PM by ivan_cruz RSS

    Concatenating and RecNo() problem

    phersan_axis

      Here is the nature of my problem:

      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:

       

      Master QVD

      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.

       

       

      Update QVD

      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?

        • Concatenating and RecNo() problem

          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.

          Load *

          from historical.qvd (qvd);

          concatenate

          load *, rowno() as ID

          from new.qvd (qvd);

          Regards