Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
robert99
Valued Contributor II

Incremental load using a unique sequential ID

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.

1 Solution

Accepted Solutions
robert99
Valued Contributor II

Re: Incremental load using a unique sequential ID

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

View solution in original post

6 Replies
MVP & Luminary
MVP & Luminary

Re: Incremental load using a unique sequential ID

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
Valued Contributor II

Re: Incremental load using a unique sequential ID

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

MVP & Luminary
MVP & Luminary

Re: Incremental load using a unique sequential ID

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

MVP & Luminary
MVP & Luminary

Re: Incremental load using a unique sequential ID

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

- Marcus

robert99
Valued Contributor II

Re: Incremental load using a unique sequential ID

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
Valued Contributor II

Re: Incremental load using a unique sequential ID

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

View solution in original post