topic Re: Variable to get the max(date) in QlikView

Variable to get the max(date)

Sat, 18 Jun 2011 07:17:10 GMT

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.

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

Variable to get the max(date)

Sat, 18 Jun 2011 08:09:35 GMT

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

Re: Variable to get the max(date)

Sat, 18 Jun 2011 08:49:09 GMT

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

Re: Variable to get the max(date)

Sat, 18 Jun 2011 09:23:02 GMT

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

Re: Variable to get the max(date)

Sat, 18 Jun 2011 11:59:42 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 05:08:55 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 05:34:36 GMT

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.

Re: Variable to get the max(date)

Mon, 20 Jun 2011 06:26:00 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 06:39:58 GMT

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;

Re: Variable to get the max(date)

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 06:50:38 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 07:01:56 GMT

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

Re: Variable to get the max(date)

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 07:11:14 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 07:24:32 GMT

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

Re: Variable to get the max(date)

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 07:31:12 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 08:58:42 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 09:11:31 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 09:32:22 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 10:19:30 GMT

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

Re: Variable to get the max(date)

Mon, 20 Jun 2011 11:30:40 GMT

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

Re: Variable to get the max(date)

Miguel_Angel_Baeyens — Mon, 20 Jun 2011 11:55:24 GMT

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