topic Load only new values from a file in QlikView

Load only new values from a file

Fri, 10 Jul 2009 19:29:22 GMT

Hello.

I have a file I need to load every month (e.g. a log) and this is the procedure I'm aiming to:

load old values stored in a qvd file
load the new files from the log
save the new table in a qvd file

Consider that in the log file I'm gonna import there could be rows already loaded in the qvd file, and I don't want duplicates.

Do you know any best practice to do what I'm looking for, please? I've tried with something like this, but with no luck:

LOAD id FROM <path>\file.qvd (qvd)
NewValues:
LOAD @1, @2 ... FROM <path>\log.txt WHERE NOT EXISTS(id)
CONCATENATE LOAD * FROM <path>\file.qvd (qvd)
STORE NewValues INTO <path>\file.qvd (qvd)

I always import all the values, or I get error stating that such table doesn't exist.

Load only new values from a file

Fri, 10 Jul 2009 19:34:33 GMT

Hi:

First you load you qvd:

Mytable:

load aa,bb from xxx.qvd;

Then you load you new data from your file

Mytable:

Load aa,bb from zzzz.log where not exists(aa); // aa is my key in the example

store MyTable into xxx.qvd;

Rgds,

Sébastien

Load only new values from a file

Fri, 10 Jul 2009 20:57:37 GMT

Thank you, Spastor.

Unfortunately it seems I have another inner problem: I've tried your solution also before posting here, but now that you confirmed that it should work, it seems that the true problem is anoter. This is my code:

NewReport:
LOAD * FROM <path>\file.qvd (qvd);

NewReport:
LOAD
if (@1='04', @2, Peek('utente',-1)) As user,
if (@1='05', @2) As type,
if (@1='05', @3, Null()) As class,
if (@1='05', @4, Null()) As number,
if (@1='05', Date(Date#(@5,'YYMMDD'),'DD/MM/YYYY'), Null()) As date,
if (@1='05', @6, Null()) As time,
if (@1='05', @7, Null()) As length
if (@1='05', @8, Null()) As cost;

LOAD @1,
 @2,
 @3,
 @4,
 @5,
 @6,
 @7,
 @8
FROM <path>\filetobeimported.dat (ansi, txt, delimiter is '\t', no labels, msq)
WHERE @1 = '04' OR @1 = '05' AND NOT EXISTS(date);
STORE NewReport INTO <path>\file.qvd (qvd);

It says that "date" doesn't exist, and in fact it is Date(Date#(@5,'YYMMDD'),'DD/MM/YYYY'). But to have EXISTS(...) to work I need both variable to have the same name.

Any idea, please?

Load only new values from a file

Fri, 10 Jul 2009 21:02:53 GMT

Oh, by the way, I also tried to place the EXISTS clause in the first load, the one with all the IF and where "date" is declared, but with no results.

Load only new values from a file

prieper — Sat, 11 Jul 2009 03:18:02 GMT

Hi,

think that you should put the WHERE-clause in the preceding LOAD.

Am not sure, how QV handles the logic queries, but would use also some brackets like

WHERE (@1 = '04' OR @1 = '05') AND NOT EXISTS(date).

HTH

Peter

Load only new values from a file

Tue, 14 Jul 2009 17:26:45 GMT

I would need some examples ot be sure that there is no errors in my script but I would do it that way:

NewReport:
LOAD * FROM <path>\file.qvd (qvd);

NewReport:
LOAD
if (@1='04', @2, Peek('utente',-1)) As user,
if (@1='05', @2) As type,
if (@1='05', @3, Null()) As class,
if (@1='05', @4, Null()) As number,
if (@1='05', Date(Date#(@5,'YYMMDD'),'DD/MM/YYYY'), Null()) As [date],
if (@1='05', @6, Null()) As time,
if (@1='05', @7, Null()) As length
if (@1='05', @8, Null()) As cost;

FROM <path>\filetobeimported.dat (ansi, txt, delimiter is '\t', no labels, msq)
WHERE (@1 = '04' OR @1 = '05') AND NOT EXISTS(Date(Date#(@5,'YYMMDD'),'DD/MM/YYYY'),
[date]);
STORE NewReport INTO <path>\file.qvd (qvd);

Your script is telling you that date is not existing because you create the field name when you load your data.
Hope this helps
( I don't have QV installed on my computer right now so there coud have some mistakes in my code)
Rgds
Sébastien

Load only new values from a file

Tue, 14 Jul 2009 17:31:36 GMT

Thank you all! I think I should have some spare minutes this afternoon to check your solutions, and then I'll let you know.

Thank you anyway for yout time!

Load only new values from a file

amit_shetty78 — Wed, 15 Jul 2009 01:45:35 GMT

You may want to see the qlikview help for loading incremental data using qvd if you have not gone thru it yet. It can be of good assist to you.

-Amit.

Load only new values from a file

Wed, 15 Jul 2009 17:04:56 GMT

Thank you.

The "buffer" solutions seems really nice, but unfortunately it doesn't seem to work in my example. Looks like Qlik can't determine which row has been already loaded.

I've tried

BUFFER (Incremental) LOAD
if (@1='04', @2, [...];
LOAD @1, [...]
FROM <path>\test2.dat (ansi, txt, delimiter is '\t', no labels)
WHERE (@1 = '04' OR @1 = '05');

even loading and storing explicitely the qvd file, but the script keeps loading all the data.

With the classic approach, the one with WHERE EXISTS() clause, I have in mind what it's needed to be done, but I can't implement it in Qlik. The script must read only the rows where "date" is not present, then store it in the qvd file.

I'm really frustrated being not able to solve such a easy task, and I'm sorry to bother you about this...

Load only new values from a file

Wed, 15 Jul 2009 17:28:42 GMT

Try the following code if it works:-

LOAD id FROM <path>\file.qvd (qvd);

NewValues:

noconcatenate
LOAD @1, @2 ... FROM <path>\log.txt WHERE NOT EXISTS(id);
CONCATENATE LOAD * FROM <path>\file.qvd (qvd);
STORE NewValues INTO <path>\file.qvd (qvd);

Thanks & Best Regards,

Kuldeep Tak

Load only new values from a file

Mon, 20 Jul 2009 22:48:42 GMT

Finally it seems I've found the way out for this problem. All your solutions were mainly correct, but I think that the main issue was related to the way of retrieveing data from the file:

LOAD if(...);
LOAD @1, @2... FROM path/file.log [...]

Now everything seems fixed with a code like this:

NewReport:
LOAD * FROM<path>/file.qvd (qvd);

LOAD
if (@1='04', @2, Peek('utente',-1)) As utente,
if (@1='05', @2) As tipologia,
if (@1='05', @3, Null()) As classe,
if (@1='05', @4, Null()) As numero_chiamato,
if (@1='05', Date(Date#(@5,'YYMMDD'),'DD/MM/YYYY'), Null()) As data,
if (@1='05', @6, Null()) As ora,
if (@1='05', @7, Null()) As durata,
if (@1='05', @8, Null()) As costo;

LOAD @1,
 @2,
 @3,
 @4,
 @5,
 @6,
 @7,
 @8
FROM <path>\test.dat (ansi, txt, delimiter is '\t', no labels)
WHERE (@1 = '04' OR @1 = '05');

LOAD *, utente&data&ora as ID /*** solving string ***/
RESIDENT NewReport;

STORE NewReport INTO <path>\file.qvd;

I still have to understand why it works even without the NOT EXISTS condition, but actually it does.

Thank you very much for the help!