Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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

Hi All

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

Login:

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;

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

Login:


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

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

3 Replies

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

Hi,

    Here is your solution.

    Save both file at same location.

Regards,

Kaushik Solanki

Not applicable

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

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

Not applicable

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

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

Community Browser