Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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')) thenLogin:
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);
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')) thenLogin:
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