i want to report company applications in Qlik Sense. In the data model the applications have a start and an end date, which are managed in a seperate table like this:
Application Table:
Application
ID
App1
1
App2
2
App3
3
Lifecycle table:
AppID
Type
Date
1
Start
01.01.2013
1
End
01.01.2019
2
Start
09.04.2011
3
End
30.08.2016
The tables come from a database, so I have no influence on that.
I want to create a new table, so i can have a year dimension. It would lookk like this:
Year:
Year
2015
2016
2017
2018
Application to Year:
Year
AppID
2016
1
2016
2
2017
1
How can I create these tables, especially the connection table, within the data editor?
One way would be to join your start-date and end-date together:
FromTo:
load AppID, Date as [From Date] from Lifecycle where Type = 'Start';
left join
load AppID, Date as [To Date] from Lifecycle where Type = 'End';
and matching them with an IntervalMatch to a master-calendar: How to use - Master-Calendar and Date-Values. Then you will have period fields like a year and could use simple expressions like count(AppID) to get the number per year.
