Count days Interval

[#1]

Hi,

I can´t get an expression to count the days using interval match. Please see attached example. Not sure if the code (interval match) is correct for this case. I need to be able to select dates (month, year etc..) and the count should be correct.

Maybe I should only use Enddate- startdate in the script but this give me problems when using the date selections.

Thanks for your help.

[johnw]

I'm not sure what you're asking. If I understand your data, Startdate is when the trip started, and Enddate is when the trip ended. So while the intervalmatch may be useful for linking the trip ledger entries up to the trips, I don't see how it has anything to do with knowing how long the trip took. The duration of the trip seems like a simple subtraction.

Or let's say a trip began Nov 1, 2010 and ends Nov 18, 2010. So the trip itself is 17-18 days depending on what you call a day. But if you select the second week of November, do you only want to see 7 days? So you want to see that this trip was active during that week, and was active for all 7 days of that week?

If so, an intervalmatch is one approach that should work, but if you're matching to your trip ledger, you'd want one entry per day. Your trip ledger seems like a poor table, as it seems to be a combination of a calendar and trip information. The calendar should be a separate table, linked only by date to the trip data.

It also appears that you don't need an intervalmatch, as it appears that you can already join your tables together without any reference to the start date and end date. The intervalmatch looks like it would be messing up the data even worse as written.

So basically, you appear to have a mess, and it's so much of a mess that I can't even tell what you intended, so I can't help you clean it up. Also, when posting examples where you want someone to fix your script, loading from Excel files means that we can't test any changes. Inline or autogenerated loads are preferable so that the changes can be tested, and without downloading additional files.

[#3]

Thanks John,

I will get rid of the intervalmatch and clean up the data with the calendertable. I only want to as you say:

John Witherspoon wrote:
Or let's say a trip began Nov 1, 2010 and ends Nov 18, 2010. So the trip itself is 17-18 days depending on what you call a day. But if you select the second week of November, do you only want to see 7 days? So you want to see that this trip was active during that week, and was active for all 7 days of that week?<div></div>

How would I get if the trip was active during that week (for all 7 days)?

Thanks

[johnw · Accepted solution]

Well, I don't know if this would be helpful or not, but it's one approach to satisfying that particular requirement. It looks like you have a table of trips like this:

Trips:
Trip, StartDate, EndDate
1, 01/11/2010, 18/11/2010

You can make a new table to hold the dates for each trip:

TripDates:
LOAD
Trip
,date(StartDate+iterno()-1) as Date
RESIDENT Trips
WHILE StartDate+iterno()-1<=EndDate
;

The while loop is essentially doing what I think you were trying to do with the intervalmatch. In this case, it's just easier and faster and uses less memory during the load to loop through the days rather than to set up an intervalmatch.

So now let's say you select the second week of November, 08/11/2010 - 14/11/2010. That gives you seven rows for Trip 1, which is what we wanted. If trip 1 were selected or were a dimension in a table, you could use count(distinct Date). If not, you might need to do something like count(distinct Trip&Date) to get a count of trip dates, assuming that if there were two trips during that week, you'd want a count of 14 instead of 7. You can also ask questions like how many trips took place during that week, count(distinct Trip).

[#5]

Thanks John, this seems to work. I will verify the answer tomorrow when I can do the testing.