topic Re: Incremental load using a unique sequential ID in QlikView

Incremental load using a unique sequential ID

robert99 — Tue, 09 Sep 2014 11:50:12 GMT

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

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.

Re: Incremental load using a unique sequential ID

marcus_sommer — Tue, 09 Sep 2014 12:31:23 GMT

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

Re: Incremental load using a unique sequential ID

robert99 — Tue, 09 Sep 2014 12:42:19 GMT

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

Re: Incremental load using a unique sequential ID

marcus_sommer — Tue, 09 Sep 2014 12:58:05 GMT

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

Re: Incremental load using a unique sequential ID

marcus_sommer — Tue, 09 Sep 2014 13:12:43 GMT

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

- Marcus

Re: Incremental load using a unique sequential ID

robert99 — Tue, 09 Sep 2014 14:05:33 GMT

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

Re: Incremental load using a unique sequential ID

robert99 — Thu, 11 Sep 2014 09:59:35 GMT

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