topic Re: Incremental load in QlikView

Incremental load

Tue, 06 Mar 2012 16:25:25 GMT

Hi,

Anybody can help me on setting Incremental load on the below query.

I dont want to reload the old data every time I clcik on reload..

I want just the new data to be added.

Thanks

LOAD Distinct

CallID,

OriginalParty,

FROM

The X directory receives new log files every 15 minutes and I want just to load the new files and add them.

Incremental load

Tue, 06 Mar 2012 17:17:59 GMT

LOAD Distinct

CallID,

OriginalParty,

FROM

////////////////////////////////////////////Make it QVD File

CDR_Temp:
LOAD Distinct

CallID,

OriginalParty,

FROM

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

//////////////////////////////////////then it will read first the QVD File, next concatenate it
////////////////////////////////////// to the new data

CDR_updated:
LOAD Distinct

CallID,

OriginalParty,

FROM ..\\cdr.Qvd;

Concatenate

LOAD Distinct

CallID,

OriginalParty,

Store CDR_updated into CDR_Temp.qvd;        ////////////Then Rename the new updated file like
Drop Table CDR_updated;                     ///////////CDR_Temp.Qvd   this is the updated file                                                 ///////////that your final application will read
///////////every time you want to update, reload the                                             ///////////processs

I hope it helps!!

Re: Incremental load

rwunderlich — Tue, 06 Mar 2012 17:43:10 GMT

Assuming these are text files, just add a BUFFER prefix.

CdrTab:

BUFFER LOAD Distinct

CallID,

OriginalParty,

FROM

;

New files will be added automatically. And new rows from old files will be added as well.

-Rob

http://robwunderlich.com

Incremental load

Tue, 06 Mar 2012 20:53:47 GMT

Should I have all the first and the second loads?

it seems to me that I dont need the first load if I weant to store it in QVD.right?

Incremental load

Tue, 06 Mar 2012 20:54:16 GMT

Rob,

this didn't work ..It still loading all the files again..

Incremental load

Tue, 06 Mar 2012 21:34:16 GMT

Yes , you are right we don´t need the first table!!

Incremental load

Tue, 06 Mar 2012 21:37:50 GMT

So my script should look like this:

CDR_Temp:
LOAD Distinct

CallID,

OriginalParty,

FROM

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

//////////////////////////////////////then it will read first the QVD File, next concatenate it
////////////////////////////////////// to the new data

CDR_updated:
LOAD Distinct

CallID,

OriginalParty

FROM ..\\cdr.Qvd;

Concatenate

LOAD Distinct

CallID,

OriginalParty

Store CDR_updated into CDR_Temp.qvd;        ////////////Then Rename the new updated file like
Drop Table CDR_updated;                  ///////////CDR_Temp.Qvd   this is the updated file                                                                                                            ///////////that your final application will read
                                                                  ///////////every time you want to update, reload the processs

right?

I am just trying to make sure before I reload beause it takes about 10 hours to finish the load.

Incremental load

Tue, 06 Mar 2012 21:50:59 GMT

Also what if I have a where statement after the load?

how it can be added after the second load from QVD?

Thanks

Re: Incremental load

rwunderlich — Tue, 06 Mar 2012 21:51:40 GMT

It will load all the files the first time the BUFFER is run. Then after that it will appear to read the files -- but it really just checking filetime & size, which is very fast -- and then gets the data from the local BUFFER. It will reread all the physical files when:

1. The LOAD statement changes.

2. The script is moved to a new machine where the BUFFERed QVD doesn't exist.It will thenget created on first load.

-Rob

Incremental load

Tue, 06 Mar 2012 22:57:58 GMT

CDR_Temp_History:
LOAD Distinct

CallID,

OriginalParty,

FROM

////// this is the history table

Store CDR into CDR_Temp.QVD;
Drop Table CDR_Temp;

CDR_Temp:
LOAD Distinct

CallID,

OriginalParty,

FROM ..\\cdr.Qvd; ////// this is the history table in QVD

Concatenate

LOAD Distinct

CallID, ////// this is the updated table in excell or whatever is your case

OriginalParty

FROM CDR_Update.XLS;

Store CDR_Temp into CDR_Temp_History; ///// Now this new file is the one your final application will read.

Drop Tble CDR_Temp;

Incremental load

Wed, 07 Mar 2012 16:18:25 GMT

Hi,

I got an error which is related to the stor CDR into CDR_Temp.QVD......I think that should be:

stor CDR_Temp_History into CDR_Temp.QVD

Under concatenate... the updated load comes from the same directory...the directory receives new log files simultaneously during the day..and for my case I want to avoid reloading the old data again I want to load only the new files that haven't been loaded in a previous load. so it doesn't take another 7 hours to finish the load.

One more thing how can I add a where statement in there. Like this:

Load ..

from...

where

isnum(dateTimeOrigination) and not

(

(len(callingPartyNumber) = 7 and left(callingPartyNumber,3) = '810' )

(len(originalCalledPartyNumber) =7 and left(originalCalledPartyNumber,3) = '810')

(len(finalCalledPartyNumber) = 7 and right(finalCalledPartyNumber,3) = '810')

( len(originalCalledPartyNumber) = 0 and len(finalCalledPartyNumber) =0 )

( duration = 0)

(len(originalCalledPartyNumber) < 10 and callingPartyNumber = 4)

);

Note that I am loading all this fields.

Thank you so much for your help and hope that will be helpfull.

Incremental load

Wed, 07 Mar 2012 16:19:26 GMT

Rob,

The idea about this is to avoid long waiting load..

by using your method I am not sure if it is reloading again or not but it is still taking around 7 hours t finish the load.

Re: Incremental load

Wed, 14 Mar 2012 13:35:39 GMT

Hi,

I tried your method but something is going wrong specially that I have a where statement after the load..

I attached the file and a sample of the directory.

I would appreciate any help as i've been working on it for awhile.

Thanks,