Variable to get the max(date)

[#1]

Hi

After loading the data in the Login table to how can I get the Max(LoginDate) and store it in a variable which has to be used while appending the only the new data into the QVD.

Login:
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq);
//Help required to create the variable. Which will be used in the below script to append only those data which 
//is not there in the above table records
Store * from Login into Login.qvd;
CONCATENATE
 
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq)
WHERE LoginDate > Max(LoginDate) which will come from the variable. I need help for this please.
;
Store * from Login into Login.qvd;

Thanks

Attitude

[#2]

Attitude,

If i understood your question Properly.I`m sure we dont need to store the value in variable to take the max of valuee from you login table. Your code should look like below.

Login:

LOAD @1 as LoginDate,

     @2 as User,

     @3 as Product

FROM

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

//Help required to create the variable. Which will be used in the below script to append only those data which

//is not there in the above table records

Store * from Login into Login.qvd;

/////////////////////////EDITED PATRT OF THE SCRIPT////////////////////////////////

NEW_LOGIN_TABLE:

NOCONCATENATE

LOAD DATE(MAX(LoginDate)) AS LoginDate

RESIDENT Login;

INNERT JOIN

LOAD @1 as LoginDate,

     @2 as User,

     @3 as Product

FROM

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

CONCATENATE (Login)

LOAD * RESIDENT NEW_LOGIN_TABLE;

DROP TBALE NEW_LOGIN_TABLE;

/////////////////////////////////////////////////////////////////////////////////////

Store * from Login into Login.qvd;

Hope this helps you.

- Sridhar

[#3]

Hi Sridhar

Thanks for your help! I didn't try the solution that has been given by you as I didn't understood.

I have done something like below. Only problem in the below code is I am not checking max(LoginDate) or ReloadTime() while concatanting the 2nd text file. I am concatanating all the data from the 2nd text file into the qvd instead of loading only those data which is not there in the existing qvd.

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

Hope it is understandable about my requirement. If no please let me know!

Thanks

Attitude

[#4]

Hi Sridhar

I think what I did may not be the right way of doing it. Instead I got one more idea. I think we use the below script which is between the block(/////) in a seperate application so that this script does not executed again and again.

There is one more way of doing it. I think I need to search for some kind of function which check whether qvd is available or not. If the qvd is already available script between the block(//////) should not get executed. What do you say?

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

[#5]

Hi

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;
////////////////////////
/**************************************** 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)
;
CONCATENATE
LOAD Product, 
     LoginDate,
     User
FROM
$(Path)\SESSIONS*.LOG (utf8, txt, delimiter is '\t', embedded labels);
Store Logon into Logon.qvd (qvd); 

[#6]

Hi

Deepak suggested you to him me on this. Please try to help me out when you find free time.

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

[#7]

Hi,

I have modified your 2nd block below. what i have understood form your earlier post is , You want to load the max of logindate from Logon tabel and load the respective data from the session and status table. if that is your problem then below is the code for your problem.

What I’m doing here is creating the temp table to store max value of the logon date and and storing the same to a variable and using that variable in session &status Table to restrict data.

Logon:

LOAD LoginDate,

     User,

     Product

FROM

(qvd);

Logon_Temp:

Load Max(LoginDate) as LoginDate_Max                        //Taking the max of date from Login table

Resident Logon;

Let vStoreMaxLogin = Peek(LoginDate_Max,-1, Logon_Temp);   //storing the max date in variable

Drop table Logon_Temp; //Droping the Temp table, Since we are not going to use this in any of  our below

CONCATENATE (Logon)

LOAD @1 as LoginDate,

     @2 as User,

     @3 as Product

FROM

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

WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file;  // here vStoreMaxLogin is the

//variable which stores the max of login date. Make //sure your date format is correct.

CONCATENATE (Logon)

LOAD Product,

     LoginDate,

     User

FROM

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

WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of session log; // here vStoreMaxLogin is the

//variable which stores the max of login date. //Make sure your date format is correct.

Store Logon into Logon.qvd (qvd);

I`m attaching a sample application in which i have shown, how to take the max of value to a variable using peek() function.

Have a look at the attached application also.

Hope this may help you.

- Sridhar

Edit : Attached a application to demonstarte the max value to store in variable.

[#8]

Hi Sridhar

Thanks alot for your help. I am not sure why peek function is required to find the max(LoginDate). Anyway that is not my question now.

Now my question is when I ran the script after doing the required change as per your suggestion it says that "vStoreMaxLogin" field is not found when the debugger comes to this location. Please suggest something here for me! Let me know what changes are required here. I did exactly the same changes that you have suggested here.

CONCATENATE (Logon)
LOAD @1 as LoginDate, 
     @2 as User, 
     @3 as Product
FROM
(txt, codepage is 1252, no labels, delimiter is ',', msq)
WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file; 

Thanks

Attitude

[#9]

Hi Sridhar

I think the problem could be somewhere here(Highligted in bold). Yesterday I tried to pass the variable in it like the way you have passed but didn't work. Thats where the confusion is. Please help me out when you find free time as you must be very busy with your work.

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

(txt, codepage is 1252, no labels, delimiter is ',', msq)
WHERE Date(@1) > Max(Date#(vStoreMaxLogin,’DD/MM/YYYY’)) of text file; 

[Miguel_Angel_Baeyens]

Hi,

You need to expand the variable in the WHERE clause

WHERE Date(@1) > Max(Date#($(vStoreMaxLogin),’DD/MM/YYYY’));

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica