Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    Here is your solution.

    Save both file at same location.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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
Author

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