Concatenating and RecNo() problem

Thu, 23 Sep 2010 20:13:23 GMT

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

Thu, 23 Sep 2010 20:39:35 GMT

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

topic Concatenating and RecNo() problem in QlikView

Concatenating and RecNo() problem

Concatenating and RecNo() problem