Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RogerG
Creator
Creator

IntervalMatch, linked tables

I have a the following tables

projects:

project, activity, activitygroup, Astartdate, Afinishdate 

  • Activity1 = Stage 1 start
  • Activity2 = Stage 2 start and Stage 1 end
  • Activity4 = Stage 3 start and Stage 2 end

(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)

 
Project 1ActivtyGroup1Stage1202020212022
Project 1ActivtyGroup1Stage1000
Project 1ActivtyGroup1Stage2100
Project 1ActivtyGroup1Stage3011
Project 1ActivtyGroup2Stage1000
Project 1ActivtyGroup2Stage2000
Project 1ActivtyGroup2Stage3000
Project 2ActivtyGroup3Stage1011
Project 2ActivtyGroup3Stage2001
Project 2ActivtyGroup3Stage3000
Project 2ActivtyGroup4Stage1000
Project 2ActivtyGroup4Stage2000
Project 2ActivtyGroup4Stage3000
 

 

 

0 Replies