The simplest way is to put a timestamp into the QVD file name and then load from it with a wildcard. For example, to create:
let vDateStamp = date(today(), 'YYYYMMDD');
(txt, codepage is 1252, no labels, delimiter is ',', msq);
STORE DataFile INTO Data\DataFile_$(vDateStamp).qvd (qvd);
And then when you come to load from a folder full of dated QVD files it would simply be:
FROM Data\DataFile_*.qvd (qvd);
This assumes that there is no duplication of rows between weekly files, and all files arrive in exactly the same format. I would recommend not using * to load fields - and reference them all explicitly. This will mean if a file does come in the wrong format the create of the QVD will fail, rather than the load from QVD.
Loading from many files will mean that the load will be slightly slower than from a single QVD file and the QVD's will take up slightly more space on the disk. You could build a routine that occaisionally loads all QVD's with a wildcard (as above) and then writes out a new QVD with data from all the source files in. The source files can then be deleted.
If there is a unique key on each row then you can add bulletproofing around importing the same row more than once by using WHERE EXISTS. If there is no key you just need to be very careful.
Thanks! This is not the way client has requested for. I want to load everything in a single qvd and then append it everyweek. Managing multiple QVD's will be very difficult and as said it will affect the performance as well. Requesting you to suggest some thing as per their requirement.
If you have any doubts please let me know on that. I will try to post you more details on this soon.
I didn't try with the solution that you have provided as I need to understand everything before implementing in mine. I am trying something like below. If I am loading it for the first time as there won't be any Login.qvd created. Could which is inside the block(/////) will be executed. For next time onwards whatever the code which is there after "end if" will be executed(Which will concatenate the data into the login.qvd from the text file and as well from the session log).
One which I still wanted to do it here is I want to check the max(LoginDate) which is loaded from the text file and max(LoginDate) which is loaded from the sessions qvd. Both is there now in a single qvd(Login.qvd). Using this two max(LoginDate) I want to concatenate the data in to the Login.qvd which is there in the 2nd block.
Please guide me on how to to get the max(LoginDate) from Text file and Session log which can be used in the 2nd block. I think to get the Max(LoginDate) we need to write where condition to pull the Max(LoginDate) from the one which is loaded in the text file and Sessions Log.
I want to load only those data which is not available in the WHERE condition of the 2nd block. I tried to store the max(logindate) of both text file and session log in a varialble but it didn't work. Also tried to load the max(LoginDate) from both the text file and sessions log in a seperate table of a field but it didn't work either.
Can some please do the required change in the 2nd block of my code so that it store only those rows which is not available in the existing qvd(I need your help where the text is in BOLD letters in 2nd block). This is urgent requirement! Hope you can understand!
/**************************************** 1st Block*************************************/ ////////////////////// if isnull(filetime('C:\Login.qvd')) then
LOAD @1 as LoginDate, @2 as User, @3 as Product FROM (txt, codepage is 1252, no labels, delimiter is ',', msq);
LOAD Product, LoginDate, User FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
Something like this, perhaps, but there are probably syntax errors. The idea is to load from the QVD, set a variable to the max date on the QVD, and then use that variable in the subsequent loads. If there is no QVD, you'll use date(0), so anything from 1900 on. I also recommend using a login timestamp instead of date if that makes sense for your application.
IF filetime('Login.qvd')>0 THEN Login: LOAD LoginDate, User, Product FROM Login.qvd (QVD); MaxDate: LOAD max(Date) as MaxDate; LOAD date(fieldvalue('LoginDate',recno())) as Date AUTOGENERATE fieldvaluecount('LoginDate'); LET vMaxDate = peek('MaxDate');
DROP TABLE MaxDate; ELSE LET vMaxDate = 0; END IF
Login: // will concatenate if already exists, else will create LOAD @1 as LoginDate, @2 as User, @3 as Product FROM Status.txt (txt, codepage is 1252, no labels, delimiter is ',', msq) WHERE Date(@1) > date($(vMaxDate));
LOAD Product, LoginDate, User FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels) WHERE Date(@1) > date($(vMaxDate));