where not exist on date

[bartwelvaarts]

hi community,

I have a table with UniqueID and Period.

period contains dates and MAT ,YTD. there is also a type. which is 'month' for all the dates. MAT and YTD gets 'Reporting'.

each month i get a new dump with the last year, but i want to keep the history.

i tried to load the new file, and concatenate the old table with: where not exists date. however this does not work.

I also tried: where date not exist and type = 'month'.

when i do this hardcoded with: where date is < 01-04-2025 F.E. it does work. 

Does someone have a suggestion to fix this?

[rafaelencinas · Accepted solution]

Hi @bartwelvaarts 

Try this:

1) Load your Anual Dump:

AnualDump:

load *

;

SELECT * FROM YOURTABLE;

2) Get the min Value of this table

MinDateNAnualDump

Load min(yourdate) minDateField Resident AnualDump;

3) create a variable to get the min value

Let minDate = Peek('minDateField',0,'MinDateNAnualDump');

4) Get Data from your Actual QVD

MyTableQVD;

Load * From  [lib://yourpathqvd/yourqvd.qvd](qvd)

Where qvdDateField <= $('minDate ');

Concatenate(MyTableQVD)

Load * resident MinDateNAnualDump;

Drop Table MinDateNAnualDump;

Store MyTableQVD into [lib://yourpathqvd/yourqvd.qvd](qvd);

Bye!