topic Re: Appending each week data in a QVD! in QlikView

Appending each week data in a QVD!

Thu, 16 Jun 2011 17:47:57 GMT

Hi All

Can someone please help me out to do following thing please. With some example could be much better!

1. On weekly basis I will be getting a file called status.txt. The file name will not change.

2. It should be appended each week.

3. Need to store the current week status(status.txt) in a qvd and then load from QVD followed by loading from the new txt file.

Hope to receive reply from some one soon.

Thanks

Attitude

Appending each week data in a QVD!

stevedark — Thu, 16 Jun 2011 20:14:08 GMT

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');

DataFile:

LOAD
*

FROM WeeklyFile.txt

(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:

Data:

LOAD

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.

Hope that helps.

- Steve

Appending each week data in a QVD!

Fri, 17 Jun 2011 18:28:08 GMT

Hi Steve

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.

Thanks

Attitude

Re: Appending each week data in a QVD!

johnw — Sat, 18 Jun 2011 00:04:21 GMT

MyData:
LOAD *
FROM MyWeeklyFile.txt (blah blah blah)
;
CONCATENATE (Data)
LOAD *
FROM MyQVD.qvd (QVD)
;
STORE MyData INTO MyQVD.qvd (QVD)
;

As with Steve's suggestion, I'm assuming no duplication of rows. You may need to reference a key, or append the week onto the data as a field, or who knows what else.

Re: Appending each week data in a QVD!

Sat, 18 Jun 2011 06:21:58 GMT

Thanks John

I will try and let you know on that but I feel it may not work as I wants to do something like below.

http://community.qlik.com/message/125391#125391

Regards

Attitude

Re: Appending each week data in a QVD!

Sat, 18 Jun 2011 12:00:32 GMT

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.

/**************************************** 1st Block*************************************/
//////////////////////

if isnull(filetime('C:\Login.qvd')) then
Login:
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);
Concatenate
LOAD Product, 
     LoginDate,
     User
FROM
$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
Store Login into Login.qvd (qvd);
Drop table Logon;
end if;
////////////////////////
/**************************************** 2st Block*************************************/
Logon:
LOAD LoginDate, 
     User, 
     Product
FROM

(qvd);
CONCATENATE
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq)
;
CONCATENATE
LOAD Product, 
     LoginDate,
     User
FROM
$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
Store Logon into Logon.qvd (qvd);

Re: Appending each week data in a QVD!

Mon, 20 Jun 2011 05:06:45 GMT

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
Login:
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);
Concatenate
LOAD Product, 
     LoginDate,
     User
FROM
$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
Store Login into Login.qvd (qvd);
Drop table Logon;
end if;
////////////////////////
/**************************************** 2nd Block*************************************/
Logon:
LOAD LoginDate, 
     User, 
     Product
FROM

(qvd);
CONCATENATE
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq)
WHERE Date(@1) > Max(LoginDate) of text file;
  
CONCATENATE
LOAD Product, 
     LoginDate,
     User
FROM
$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
WHERE Date(@1) > Max(LoginDate) of session log;
Store Logon into Logon.qvd (qvd);

Thanks

Attitude

Re: Appending each week data in a QVD!

johnw — Tue, 21 Jun 2011 00:01:42 GMT

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.

DIRECTORY C:\;

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));

CONCATENATE (Login)

LOAD Product,
LoginDate,
User
FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
WHERE Date(@1) > date($(vMaxDate));

STORE Login INTO Login.qvd (QVD);

Re: Appending each week data in a QVD!

Tue, 21 Jun 2011 04:58:19 GMT

Thanks John

I think I need to understand the solution first before implementing on mine. Right now I am trying something the same script which I am working these days. Once done with that I will try with yours.

Regards

Attitude