Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

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
Highlighted
Specialist III
Specialist III

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
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist III
Specialist III

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
MVP & Luminary
MVP & Luminary

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

- Marcus

Highlighted
Specialist III
Specialist III

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

Highlighted
Specialist III
Specialist III

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