Incremental load

[#1]

Hi,

Anybody can help me on this issue..

I've been working on it for over three weeks!!

I am loading phone log files"TXT" from a directory and the directory receives new log files every few minutes... and it is a hug directory"it takes over 8 hours to finish loading".

Is there anybody can help me in setting incremental load so I dont load all the files again.

It will be great if i can load to a QVD file and have the incremental load to there and then I can load from the qvd to my application.

Attached is the QVW and a sample of the directory.

Thanks

[#2]

I tried to the following and it didnt work

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;

[Oleg_Troyansky]

Badr,

incremental load for log files is described very well in the Help section, in the article called "Using QVD Files for Inccremental load". Just search Help section for "Incremental Load" and you'll find it. It has very detailed explanations, code examples, etc...

cheers,

Oleg

[#4]

I tried to follow the same method and apply it to my data .. but I run into error : field not found <#2012>

Which I have no idea where is this field coming from!

[swuehl]

Hard to say. Could you post your current script here?

I noticed that you are having a slightly different setting, you are accessing a lot of different log files, each seem to have a timestamp in the filename, and at the very end something that looks like a log file sequence number, is that correct?

It might then be more useful to not look at a timestamp taken from the records (i.e. a Call timestamp), but to the file sequence numbers themselves. You can load all filename in a list and filter only the filenames with a sequence number greater than the last read.

Something like using

for each File in filelist(PATH&'cdr*')

CHECK FOR SEQUENCENUMBER AND LOAD

next File

Though it seems that you are handling a lot of files, but mostly small files. I am not sure what the performance penalty in looping over the filenames is compared to doing a wildcard load with a where clause.

[swuehl]

I was actually talking about not using record information for the incremental load decision, but the filenames themselves.

Please see attached.

I assume that the last number in your filename is a sequential sequence number, at least it looks like one. So after loading the initially stored QVD, I only load files with a sequence number larger than already read in.

Looking at your code, I think you miss actually using the QVD. You store your table to the QVD at the end of the script, but you need to read it in in the next load somewhere, too. I am also not sure if the loads in your script won't just read in all the records all the time, hence always perform a full load.

I think a nice template for a classic incremental load is the example in the QV cookbook, available from Rob Wunderlichs download site. You might want to look at that, it's well documented and I just copied some lines to my attached sample (my code is a little different, since I am using filenames instead of records and not coping with modified or deleted records).

Hope this helps,

Stefan

[#7]

Hi,

I tried to run the above atachement and I am getting an error that says: Table CDRTable not found.

Any advice?

Thxs,

Badr

[swuehl]

Most likely your path to the logfiles is not correct, you need to adapt this line to your settings:

SET vLogPath='..\log files test\log files test\';  //Adapt path to log file location folder

[#9]

Hi again,

The path specified in my query is : SET vLogPath='..\log files test'; as I have the qvw and the log flies directory in the same directory.

by debuging the script: the path shows as follow:

Once it gets to debug this line:

for each File in filelist('$(vLogPath)cdr*')

I get:

File    "C:\QlikViewReports\WorkingDocuments\log files test\cdr_StandAloneCluster_02_201203010519_557424"

vLogPath    "..\log files test\"

and the script finishes with failure to load the data.

Thxs,

[swuehl]

If you have the qvw and the log files directory in the same directory, I think it should be

SET vLogPath='.\log files test\';

only one dot at the start, indicating current directory, and don't forget the final back slash.

(Though your debug output for File seems to give a valid filename, strange).

Could you zip your current directory structure with the qvw and some logs and load it up?