Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

Hi All

I have table called as Login. In this table file will be loaded from the txt file and from qlikview sessions log file. First we would want to store the data from both(txt file and sessions log) in a qvd. Then we would like to concatenate and store it in the qvd the every new text file and data from the sessions log which is not there in the previous qvd.

Login:

//Following text file should be stored in a QVD
//Everyweek we will receive on text file
//File name will remain same(status.txt)
//Everyweek new text file should be appended to QVD data(Previous data).


LOAD @1 as LoginDate,
     @2 as User,
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

//Data is loaded in the below table from the sessions log
//It has to be concatenated to the above table
//Everytime the data will be loaded from the same path
//Like above every week new data has to be appended like above

Load

LoginDate,
User,
Product

FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);

To work on the above requirement. We got the request to do something like below. Could you please review and let me know whether it will workout or not please. I am little confused here! Now sure from where to start.

LOAD * FROM $(folder)Login (qvd);

//Max(LoginDate)  or last reload from the text file
//Max(LoginDate)  or last reload from the sessions log load

CONCATENATE

Load
LoginDate,
User,
Product
FROM $(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels)
Where LoginDate > Max(LoginDate)  or last reload from the sessions log load;

CONCATENATE

LOAD @1 as LoginDate,
     @2 as User,
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq)
Where @1 > Max(LoginDate)  or last reload from the text file;

STORE Login into $(vfolder)Login.QVD;

2 Replies
Not applicable

Re: Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

Hi Koushik

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

Thanks

Atttitude

Not applicable

Re: Loading data from text file and sessions log on weekly basis in a qvd. Only new data to be appended to the QVD.

Hi

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

Community Browser