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

Sorry for coming back to you again and again. As you said Max() will return a numeric. I tried something like below. When I saw the status in debugger mode it is not loading any data but it is throwing error message such as "Execution of script failed. Reload old data?". What could be the reason for this? Any suggestion please!

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

Still I am trying from my side and as suggested I am trying to understand how it works as well :-). So will update you later on!

Thanks

Attitude

[#3]

Hi,

You dont need to use max again in where clause script , since the variable which we have created is already storing the max value, you need to silmply use the variable and equate to the @1 field .

Your code should look like below one.

Where Date(@1,'DD/MM/YYYY') > Date($(vStoreMaxLogin),'DD/MM/YYYY');

Your code should look like below one.

- Sridhar

[#4]

Hi Sridhar

Thanks! I have already noticed that and changed it as well but it is not working. Everytime I load the data it is storing all the data from the text into the qvd again again 😞

Don't have any clue what could be the reason here. Sorry if it is so irritating for you.

Thanks

Attitude

[#5]

Hi Sridhar

Sorry to trouble you! I have copied my script so that it is easy for you to help me out 🙂

if isnull(filetime('C:\Login.qvd')) then
Login:
LOAD Date(@1,'DD/MM/YYYY') as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);
Concatenate
LOAD LoginDate,
     User,
     Product
FROM

(qvd);

Store Login into Login.qvd (qvd);
Drop table Logon;
end if;
Login:
LOAD LoginDate, 
     User, 
     Product
FROM

(qvd);
MAX1:
Load Max(Date(LoginDate)) as MaxDate1 Resident Login where WILDMATCH (Product,'*abc*');
Let vMax1 = Peek('MaxDate1',-1, MAX1); 
//Drop table MAXDATE1;
MAX2:
Load Max(Date(LoginDate)) as MaxDate2 Resident Login where WILDMATCH (Product,'*cde*');
Let vMax2 = Peek('MaxDate2',-1, MAX2);
//Drop table MAX2; 
//CONCATENATE
//Logon:
//CONCATENATE(Login)
//Concatenate
Login:
LOAD Date(@1,'DD/MM/YYYY') as LoginDate, 
     @2 as User, 
     @3 as Product
FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq)
Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY'); 
Store Login into Login.qvd (qvd);

[Miguel_Angel_Baeyens]

Hi,

You have to drop table Login, otherwise, since all tables you are loading are identical, they are being concatenated implicitly.

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

yes, i about to tell you what miguel was telling.

if isnull(filetime('C:\Login.qvd')) then

Login:

LOAD Date(@1,'DD/MM/YYYY') as LoginDate,

     @2 as User,

     @3 as Product

FROM

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

Concatenate

LOAD LoginDate,

     User,

     Product

FROM

(qvd);

Store Login into Login.qvd (qvd);

Drop table Logon;

end if;

Login:

LOAD LoginDate,

     User,

     Product

FROM

(qvd);

MAX1:

Load Max(Date(LoginDate)) as MaxDate1 Resident Login where WILDMATCH (Product,'*abc*');

Let vMax1 = Peek('MaxDate1',-1, MAX1);

//Drop table MAXDATE1;

MAX2:

Load Max(Date(LoginDate)) as MaxDate2 Resident Login where WILDMATCH (Product,'*cde*');

Let vMax2 = Peek('MaxDate2',-1, MAX2);

//Drop table MAX2;

//CONCATENATE

//Logon:

//CONCATENATE(Login)

//Concatenate

Drop Table Login,MAX1,MAX2 ;  ///Drop the table here.

Login:

LOAD Date(@1,'DD/MM/YYYY') as LoginDate,

     @2 as User,

     @3 as Product

FROM

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

Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY');

Store Login into Login.qvd (qvd);

[#8]

Hi Miguel/Sridhar

If drop Login,MAX1 and MAX2. QVD contains contains only those records which are getting loaded after the drop statement. Whereas I want to concatenate those records which are getting loaded after the drop statement.

Hope you have understood now regarding what exactly I am looking for.

Also once all the records which is there after the drop statement gets concatenated then from next time when I load any if no new records are there then no rows will be concatenated. If any new records are there then only those new records should be concatenated.

Thanks

Atttitude

[#9]

Hi Miguel/Sridhar

Below script doesn't seems to be working fine. As it is loading all the data again and again in the qvd instead of loading only new rows. Only for the first time I want it to load all the data as anyway it will be new only. Thats the reason I want to use the below where condition.

Where Date(@1,'DD/MM/YYYY') > Date($(vMax1),'DD/MM/YYYY'); 

Thanks

Attitude

[#10]

Hi Miguel/Sridhar

Do you have any other suggestion? I am not able to make it out where I am doing the mistake. I am totally confused here. Please help me out!

Thanks

Attitude

[#11]

Attitude,

As far i know, the above code should work.

One debugging step would be, take this vMax1 variable to text box after loading and check whether this variable is giving value or not. if this variable gives value then check manually, whether captured value is the max of the logindate from you Login table.

Try commenting one by one table script and load them one by one and try to trouble shoot.

- Sridhar