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]

Hi Miguel

I have also thought of samething and did the required changes in it same like what you have suggested for but it didn't work. 😞

I got the follow error when I tried with the solution that you have provided.

Error in expression:
')' expected

I would appreciate if you give any other suggestion.

Thanks

Attitude

[Miguel_Angel_Baeyens]

Hi,

Just follow the error and close the missing bracket (if there is one)

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

By the way, note that we cannot check every line of the code we post, and sometimes we give ideas based on your code, and that code may have syntax errors or mispellings. It's always a good idea to not just copy and paste the code you get here, rather than check it first, understand what it does and how can it help you and then apply it to your data model and script.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Hi Miguel

I am sorry If I am disturbing you. I too agree with you that whatever we do we need to understand and do. I always do that! Only sometimes when I get stuck I get confused.

I followed the error but there is no bracket which needs to be closed. Thats the reason I asked you are you able to find where I have done the mistake?

Thanks

Attitude

[Miguel_Angel_Baeyens]

Hi,

There's no problem, it's just you should be able to debug the load and see where the variables or the code is not correct. If it says it's expecting a bracket, it may be because the "Max()" is not getting the value from the variable properly.

Did you try using that Max() piece of code in a textbox to see whether it returns the expected result?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Hi,

Miguel is correct, I forget to include the dollar sign expansion on my code.

Look at the attached application with dollar sign expansion.

-Sridhar

[#7]

Hi Sridhar

Yes! I know that Miguel is correct :-). I have already added the $ symbol and tried but it is not working. Please look at discussion with Miguel for more details.

Thanks

Attitude

[#8]

Yes, i read all your discussion with Miguel`s. I though, you are not sure where / how to added the new piece of code to your application, That's why I have attached my example by re-define the code on my last post.

is it throwing the error after you have added that new code to your application. could you please post your entire scripts here.

- Sridhar

[#9]

Hi Sridhar

Yes it is giving below error. If you don't mind can you please wait for hours I will try to send you the entire script. Before that any guesses what could be the reason for the below error. 

Error in expression:
')' expected

[#10]

Hi Sridhar

Below is exactly the script which I am using it in my application. Requesting you to review it and let me know what is the change that is required here. It is little urgent. Hope you can understand! I will be waiting for your reply...

/**************************************** 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);
Logon_Temp:
Load Max(LoginDate) as LoginDate_Max Resident Logon:
Let vStoreMaxLogin = Peek(LoginDate_Max,-1, Logon_Temp);
Drop table Logon_Temp;

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

[Miguel_Angel_Baeyens]

Hi,

This is what I see at a first glance (usually typos when copying / pasting)

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

Logon_Temp:

Load Max(LoginDate) as LoginDate_Max Resident Logon; // semicolon instead of colon

Let vStoreMaxLogin = Peek('LoginDate_Max', -1, 'Logon_Temp'); // Field name and table must be quoted
   
Drop table Logon_Temp;

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')); // Use regular single quotes instead
                                 // Max() will return a numeric value, Date() will return 
//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); 

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica