topic Storing only the filtered records in the existing QVD. Based on the Max(LoginDate) from existing QVD. in QlikView

Storing only the filtered records in the existing QVD. Based on the Max(LoginDate) from existing QVD.

Mon, 20 Jun 2011 12:02:47 GMT

Hi All

Can some one please help me out with the following requirement which is there in between the block(//////////). It is little urgent!

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

drop table Login;

Select LoginDate, User, Product
FROM

(qvd);

///////////////////////// Requirement /////////////////////////

1. Using the Max(LoginDate) from the Login. I need to filter data which has to be concatenated with the Login.qvd from Status.txt

2. Only those records in which LoginDate > Max(LoginDate){Which is loaded from the above qvd file} should store in to the existing qvd(Login.qvd)

3. For the above 2 requirement what should be there in the below "WHERE" condition?

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

CONCATENATE

LOAD @1 as LoginDate,
@2 as User,
@3 as Product
FROM

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

Re: Storing only the filtered records in the existing QVD. Based on the Max(LoginDate) from existing QVD.

kaushiknsolanki — Mon, 20 Jun 2011 12:34:53 GMT

Hi,

Here is your solution.

Save both file at same location.

Regards,

Kaushik Solanki

Storing only the filtered records in the existing QVD. Based on the Max(LoginDate) from existing QVD.

Mon, 20 Jun 2011 13:29:05 GMT

Hi Kaushik

Thanks for your effort and time!

Looks like it is working fine. Though I do the same thing here it is not working in mine. I think the problem may be because of the date format. I will try to work more on it and will update you on this. Long time I am trying in mine but it is not working at all 😞

Thanks

Attitude

Re: Storing only the filtered records in the existing QVD. Based on the Max(LoginDate) from existing QVD.

Mon, 20 Jun 2011 14:35:12 GMT

Hi Kaushik

Sorry to trouble you! I have copied my script so that it is easy for you to help me out 🙂 I can attach the application but it takes time.

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