Load only new values from a file

[#1]

Hello.

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

1. load old values stored in a qvd file
2. load the new files from the log
3. 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.

[#2]

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

[#3]

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?

[#4]

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.

[prieper]

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

[#6]

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

[#7]

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!

[amit_shetty78]

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.

[#9]

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...

[#10]

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