how to build a daily census/reporting link from a table of start/stop times

[daveatkins]

(editing this question after resolving detail issues)

I have a table which contains a history of visits which contain a start and end time. My goal is to be able to select a single date from the past and see how many visitors were present on that date. Ultimately, I will use this for comparison of this monday vs last monday, etc. by selecting a date in the new "DailyFilter" table to reduce the data to a selection of visitors who were present on that date.

The data, simplified, is like this:

visitid, startdatetime, enddatetime, [details]

What I think I need to do is create a new, "DailyFilter" table which contains every day and every visit that was present on that that day.

When I do this, the result is a problematic many-to-many join situation. As I attempt to do daily "snapshot" loads over a period of time, by reloading the resident table into a new table that was intended to map each reporting date to a set of active visits, Qlik does not concatenate each iteration and I end up with only the last date from the loop present in the DailyFilter table.

I am thinking I may need to change the whole approach here and build up a qvd file of daily snapshots. Not sure how best to do this, looking for suggestions on how others have accompished this data model of converting a set of from-to times into something that can report out the daily count of what was active on each day...but also realize there are many other dimensions that need to "come along" each day so it is not just the simple example below, but imagine each visit has additional dimensional attributes which would filter on etc.; e.g. visit type, person demographics, etc.

visit 1, peson 1, started 1/1, ended 1/5

visit 2, person 2, started 1/2, ended 1/4

desired report:

date, census

1/1, 1

1/2, 2

1/3, 2

1/4, 2

1/5, 1

[therealdees]

For what I understood, maybe you need a master calendar that holds every possible date considering the lower and upper bound date you have in your file. From there, you could use a chart expression to aggregate the counting for the specific dates, ex:

Aggr(Count(person_id), start_date, end_date) // something similar to this

Another approach would be using IntervalMatch considering you have 2 numeric fields (dates in this case).

If you provide a .qvf sample I could try getting to the desired result.

[daveatkins · Accepted solution]

This is much easier to do in SQL than Qlik. Create a temp table of all the dates and join to the source table of start and end dates with where clause containing the date range so that it produces a cross product of the dates for dates where the start and stop times are >/< than the date, count( distinct thepatients), group by etc. and then load that summary information into qlik for the visualization.

Here is the core part of the query (edited somewhat to make generic from my organization):

WHILE @MinDate <= @MaxDate
BEGIN
INSERT INTO #AllDates (CensusDate) VALUES (@MinDate);
SET @MinDate = DATEADD(DAY, 1, @MinDate);
END;

...

SELECT
ad.CensusDate,
s.FACILITYID,
s.Reason,
COUNT(DISTINCT s.AccountNumber) AS patients
FROM #AllDates ad
inner JOIN sourcetable s on s.StartDateTime <= ad.CensusDate
AND (s.StopDateTime >= ad.CensusDate or s.StopDateTime is null)
GROUP BY ad.CensusDate, s.FACILITYID, s.Reason;