If QVD already exists then I don't want to execute piece of code!

[#1]

Hi All

If the QVD already exists in the then I don't want to execute the piece of code which is there in between the block (/////). May I know how to do that please!

//////////////////////////////////////////////////////////////////
Login:

LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
     //,
     //ReloadTime() as LastReload
FROM

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

Store Logon into Login.qvd (qvd);


Drop table Login;
///////////////////////////////////////////////////////////////////
Login:


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


Store Login into Login.qvd (qvd);

Thanks

Attitude

[kaushiknsolanki]

Hi,

    Below given is nothing but the script for incremental load. This will help you to recognise that QVD is already  generated or not.

LET vQVDPath = 'Qvd\'; // Set QVD storage Directory

SET vOffset = '00:15:00';

LET vUTC = UTC();

LET vExecTime = replace(timestamp(vUTC-vOffset, 'YYYY-MM-DDXhh:mm:ssZ'), 'X','T');

// SalesForce.com uses a timestamp format of ‘YYYY-MM-DDThh:mm:ssZ’, hence the need for the replace statement here.

SET vLastExecTime = 0; // resetting vLastExecTime

// As long as a QVD already exists (the isnull() check), find the latest timestamp for modified records. This will be used to generate the delta set.

if not isnull(QVDCreateTime('$(vQVDPath)<tablename>.qvd')) then

LoadTime:

Load Max(LastModifiedDate) as LastModifiedDate // Or max(CreatedDate)

From $(vQVDPath)<tablename>.qvd (qvd);

Let vLastExecTime = replace(timestamp(peek('LastModifiedDate',0,'LoadTime'),'YYYY-MMDDXhh:mm:ssZ'), 'X', 'T');

Drop Table LoadTime;

end if

SQL SELECT Id,

…

FROM <tablename>

WHERE LastModifiedDate >=$(vLastExecTime) and LastModifiedDate < $(vExecTime);

// Check to see if this is the first reload. If it is, skip this step

if Not isnull(QvdCreateTime('$(vQVDPath)<tablename>.qvd')) then

Concatenate (<tablename>)

LOAD *

FROM $(vQVDPath)<tablename>.qvd (qvd)

WHERE Not(Exists (Id));

end if

//If data exists within table, store to QVD.

if NoOfRows('<tablename>') > 0 then

STORE <tablename> INTO $(vQVDPath)<tablename>.qvd;

Drop Table <tablename>;

end if

      Hope this will help you.

Regards,

Kaushik Solanki

[#3]

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

[#4]

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