topic Date or nearest. in QlikView

Date or nearest.

Mon, 11 May 2009 22:15:28 GMT

Hi,

I have a set of scores (setA) which are recorded weekly, the Date field is the date the recording took place

01/01/2000, 08/01/2000, ....

every 7 days.

However, there are other scores (different set, setB) which are recorded daily.

The data should be related so that if i pick the 04/01/2000 (4th jan 2000), the score value for both are shown,

obviously there is no entry for 04/01/2000 in setA, however the score for that week was recorded at 08/01/2000.

Currently, when i select 04/01/2000, setB is shown but setA shows empty for the above reason. How can I make it so that setA displays the value for that week 01/01/2000 - 07/01/2000

Date or nearest.

Mon, 11 May 2009 22:18:55 GMT

I think you should be able to do an intervalmatch fonction between the Set1 dates and SetBDates.

This should give you a table with the dates matching both.

Date or nearest.

Mon, 11 May 2009 22:57:16 GMT

Ok so based on what you said I looked in the manual on page 306 and found this:

intervalmatch (Day) Select PeriodStart, PeriodEnd from

intervaltable;

So I'm guessting it would be for me given the query:

SELECT *
FROM myTable

I would have

intervalmatch (Date) Select ?????????

See I'm not sure how to use intervalmatch, what's more it seems periodstart and periodend are 2 distinct fields in a table.

Where as my information has a set of dates (which in order make up the intervals)

1/1/08 | 10
8/1/08 | 15
15/1/08 | 12

rather than

Start | End
1/1/08 | 7/1/08
7/1/08 | 14/1/08
15/1/08 | ....

Is this still ok?

How would I do it?

Date or nearest.

Mon, 11 May 2009 23:04:43 GMT

Yes I think you're on the good way.

You should rework your setA tabe in order to get a Start date and End date.

To do this you should do a Load Date as startdate, after(Date) -1 as EndDate resident matable.

Then you should be able to run the intervalmatch...

I cannot find an easiest way for the time being...

Date or nearest.

rwunderlich — Tue, 12 May 2009 02:26:59 GMT

If your setA dates are always exactly 7 days apart, it's easy to calculate the end date in the IntervalMatch.

IntervalMatch (SetBDate) LOAD SetADate, SetADate+6 RESIDENT SetA;

-Rob