Storing daily backlog. Concatenating causing duplicates

[andreas_persson]

I'm storing a daily record of a company backlog. However, I can't figure out how not to get duplicates if the script loads multiple times one day. Can someone guide me in the right direction?

I've tried where not exists() with no success.

Here's the code.

Data:
LOAD
    %client,
    "# Net Amount Order",
Date(now(),'YYYY-MM-DD') as DateStamp
   FROM [lib://en-US (ad_ext_kons)/Business Documents.qvd](qvd) ; 

Left Join(Data)
Load
%client,
sum("# Net Amount Order") as sum
resident Data 
group by %client;

Concatenate (Data)
Load %client,
sum,
DateStamp
FROM [lib://QLIKDATA/QVD\GG\Orderstock99.qvd] (qvd);

Store 
%client,
sum,
DateStamp
from Data into [lib://QLIKDATA/QVD/GG/]Orderstock99.qvd(QVD);

Thanks!
/ Andreas

[Or]

I'm kind of confused by what your script does in the first place... but Not Exists should presumably do the trick. Could you explain what you're trying to achieve, exactly?

[andreas_persson]

I'm trying to record a snapshot of the company backlog each day. 

The sum of "# Net Amount Order" is loaded with a datestamp and then stored in the QVD file. 

Next day the sum is loaded again with a new datestamp and concatenated with the stored file.

[Or · Accepted solution]

But why are you doing this in two passes? I guess that's the confusing bit. Should be something like:

Load %client,
sum,
DateStamp,

%client & DateStamp as KeyField

FROM [lib://QLIKDATA/QVD\GG\Orderstock99.qvd] (qvd);

Concatenate

LOAD
%client,
sum("# Net Amount Order") as sum
Date(now(),'YYYY-MM-DD') as DateStamp
FROM [lib://en-US (ad_ext_kons)/Business Documents.qvd](qvd)

Where Not Exists(KeyField, %client & Date(now(),'YYYY-MM-DD'))

Group By %client, Date(Today(),'YYYY-MM-DD');

Drop Field KeyField;

Note that this will de-optimize the QVD load, so you may want to save the key as part of the row in the file if you'd like the QVD read to be faster (but this will require more disk space to save).