Checking if Date field 1 is between Date field 2 and Date field 3

[Anonymous]

Hi,

I have 3 date fields in 3 separate tables - the 3 tables are joined together. I need to provide a date input in my report that allows the user to filter the results where Date field 1 (called AccessDate) is between a range of Date field 2 (called MeetingDate) and Date field 3 (called PublishedDate). So, the desired logic is, return results where AccessDate is between the chosen MeetingDate and the chosen PublishedDate.

I would like to be able to use one calendar object for the AccessDate and another calendar object for the PublishedDate, but the resultant date range is applied against the AccessDate.

Is this possible in QlikView? If it's not, are there any suggestions for implementing this some other way?

Thanks,

VB

[Andrea_Ghirardello]

You should use IntervalMatch

http://community.qlik.com/docs/DOC-4310

[techvarun]

Check this link

http://community.qlik.com/thread/82899

Hope it helps

Reghards

[Anonymous]

Thank you.

The MeetingDate and PublishedDate fields come from one table and the AccessDate field comes from another table - these two tables are then joined together into one table named DATE_ACCESS - please see below and please see the IntervalMatch code:

DATE_ACCESS:

  LOAD Distinct

  EMP_FULL_NAME as "Employee",

  COMMITTEE_ID,

  MeetingDate,

  PublishedDate

  From [$(vLocalDevPath)\TABLE1.qvd] (qvd);

  Left Join(DATE_ACCESS)

  LOAD Distinct

  COMMITTEE_ID,

  APPL_NAME as Application,

  AccessDate

  From [$(vLocalDevPath)\TABLE2.qvd] (qvd);

  DateMatchTemp:

  IntervalMatch (AccessDate) LOAD Distinct MeetingDate, PublishedDate Resident DATE_ACCESS;

  INNER JOIN (DATE_ACCESS)

  LOAD

  MeetingDate & '-' & PublishedDate AS DateKey,

  AccessDate

  RESIDENT DateMatchTemp;

  DROP TABLE DateMatchTemp;

The report will have two calendar objects - one for MeetingDate and one for PublishedDate; my question is, how do I implement the IntervalMatch code in the GUI, using the two calendar objects, so that the logic returns the data where the AccessDate is between the chosen MeetingDate and PublishedDate values? What changes have to be made to the relevant sheet objects?

[jaspal_icon]

Hi VB,

If I understood Well, then you need to Filter The Records on the basis of "Access Date". Which you are saying is depending on two different Dates, 1. Meeting Date, and 2. Published Date. Am I Right?

If you already have two separate calender then problem is solved, that take vStart Varibale date from Meeting date Calnder and vEnd Variable date from Published Date.

This way you are not selecting anything in data model, that means all data is present not filtered.

Now you can make your expression like:

Count(If(AccessDate>=vStart and AccessDate<=vEnd, ID))

I guess this should work, if not then reply back.

Thanks

Regards