IntervalMatch, linked tables

RogerG — Mon, 25 Jan 2021 23:42:19 GMT

I have a the following tables

projects:

project, activity, activitygroup, Astartdate, Afinishdate

(the above works fine)

cost:

activity, account, costdate, amount

I am pulling the project, activity and activity group from the projects table

then getting the earliest activity datestart as the stagedate (activities can be repeated over time)
then using logic with peek and other if conditions, I extract the Stage Date as the StageStartdate and StageEndDate

(The above works fine)

then

pulling activity and cost from the costdate

then left join intervalmatch (costdate) load StageStartdate , StageEndDateresident projects;
this works ok to pull in the costs, but what the interval match is supposed to define is the stage of the costs go into.

(here, I am having my costs replicated across the stages or accorss the activity groups when I try to create a "Outer join" type table)

I am trying to generate a pivot table as below. even if there is a row with 0 costs, it still needs to be present

project, activitygroup,stage,Stage, amount (by year)

topic IntervalMatch, linked tables in QlikView