Incremental load using a unique sequential ID

[robert99]

At work Ive been told (I need to confirm this) that in the main ERP system

Records are never changed or deleted

Records (lines) always have a modification time and numerical unique ID

Based on this it seems an ID (rather than modification time) could be used for an incremental load.

What are the pros and Cons of using an ID rather than time?

How should this be done?

I have listed one attempt below. Is this OK or is there a better way?

DATALOAD:

LOAD ID,

     Type

FROM

(qvd);

IDPEEK:

LOAD

ID

RESIDENT DATALOAD

ORDER by ID;

LET vLastID = PEEK ('ID',-1,'IDPEEK') ;

DATALOAD:

Concatenate (DATALOAD)

LOAD * INLINE [

   ID, Type

    100, AAA

    101, BBB

    102, CCC

    103, DDD

    104,WWW

    105,CCC

    ]

    WHERE ID > $(vLastID)

     ;

    DROP TABLE IDPEEK;

STORE DATALOAD INTO

(qvd);

Thanks for any help on this.

[marcus_sommer]

I would use the ID because it will be an integer-value and modification-time will be a float-value and mustn't be unique. Further you could use exists() as where-condition instead from IDPEEK-Load.

where not exists(ID);

- Marcus

[robert99]

Thanks Marcus

I did think of using where not exists (ID) but for some reason I thought this would be much slower to load.

So load the DVD first and then the ERP data with where not exists (ID)

But I will try both and see if there is a difference

[marcus_sommer]

Exists is the only possibility to use a where-condition with an optimized load so it is the fastest way to load - see here: http://community.qlik.com/docs/DOC-2641

- Marcus

[marcus_sommer]

Here a useful addition: http://community.qlik.com/docs/DOC-7020

- Marcus

[robert99]

Thanks

Ive tested using excel and it made little difference

The qvd load took under 1 sec (and it was optimised as I loaded everything). But the Excel load took about a minute regardless of approach used. ie if I loaded everything (No qvd)  or nothing (100% from qvd)

So excel is not the best to test with. Searching excel to see if a record should be loaded took as long as loading the record

I will test at work tomorrow and record the results. I just thought the database having a selection to be more than (using peek) would take less time than comparing every field line with another table. But I will be interested to see what the results are.

If the same then I would use where not exists

[robert99 · Accepted solution]

Testing at work finished

Where not exists did not work with the database.

I tried preceding load as well and it also did not work. Maybe I did sometime wrong ...

But peek was very quick. So I will use peek