Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
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
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
Here a useful addition: http://community.qlik.com/docs/DOC-7020
- Marcus
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
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