topic Re: Incremental load in QlikView

Incremental load

Wed, 14 Mar 2012 13:52:39 GMT

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

Incremental load

Wed, 14 Mar 2012 18:11:39 GMT

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;

Incremental load

Oleg_Troyansky — Wed, 14 Mar 2012 18:13:26 GMT

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

Incremental load

Wed, 14 Mar 2012 21:07:53 GMT

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!

Incremental load

swuehl — Wed, 14 Mar 2012 22:26:36 GMT

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.

Re: Incremental load

swuehl — Fri, 16 Mar 2012 00:24:34 GMT

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

Incremental load

Fri, 16 Mar 2012 00:57:17 GMT

Hi,

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

Any advice?

Thxs,

Badr

Re: Incremental load

swuehl — Fri, 16 Mar 2012 11:03:11 GMT

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

Incremental load

Fri, 16 Mar 2012 13:39:05 GMT

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,

Incremental load

swuehl — Fri, 16 Mar 2012 14:06:49 GMT

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?

Re: Incremental load

Fri, 16 Mar 2012 14:31:36 GMT

Here we go.

I tried the one dot as well and it seems to have the same error.

Here is the attachement.

Thank you so much for spending the time helping me.

Re: Incremental load

swuehl — Fri, 16 Mar 2012 14:47:16 GMT

There was just the trailing back slash missing.

I changed the logic, so you now specify the path including the file search string

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

maybe this makes it easier to maintain.

Attached the modified zip, should run out of the box.

Re: Incremental load

Fri, 16 Mar 2012 15:24:26 GMT

This sounds to be working fine.

How can I make sure it is not loading the files again ...I mean how can I test it?

Thxs

Incremental load

swuehl — Fri, 16 Mar 2012 15:31:30 GMT

You'll see it in the log file or by looking at the script execution dialog.

Or by debugging (could be quite painful to go through hundred of files, so start with few).

You can add TRACE statements to the script to log more information at different point (please refer to the Help for that).

On large log volumes, you should notice an effect on load time (I even noticed when using the logs provided).

Re: Incremental load

Fri, 16 Mar 2012 15:35:01 GMT

Thank you so much for your grerat help.

I'll do that.

however, you are the best QV developer in here. I guess you have been working with QV for long ..long time.

Re: Incremental load

Tue, 20 Mar 2012 18:09:09 GMT

Hey Swuehl,

After loading the following script the data is storded in a qvd which is great, but when I add more Log files to the directory it doesn't add them to the QVD file..

Any advice will be great.

Thxs,

Badr

Re: Incremental load

hectorgarcia — Tue, 20 Mar 2012 18:38:41 GMT

badr, you need to use the prefix "add" in the load statement

Hector

Re: Incremental load

Tue, 20 Mar 2012 18:41:39 GMT

I tried Add Load *

but it didnt make any different

Re: Incremental load

swuehl — Tue, 20 Mar 2012 22:32:46 GMT

I can't look into your problem in deep right now.

New files will only be read if the if condition of your script is fulfilled and records will only

be loaded when the WHERE clause is true.

So please double check both maybe by stepping through with the debugger.

I think I have tested my previous script with adding New files.

Re: Incremental load

sivarajs — Wed, 21 Mar 2012 05:04:20 GMT

Hi,

Use can use Concatenation

The script will be like

Let Qvdpath=(path);

Temp:

load * from (Qvdpath);

concatenate

load * fom(LOG file which u are loading)

store Temp into $(Qvdpath);

Hope this helps....

Regards,

Sivaraj S