Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey,
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?
I would appreciate your help!
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.
- Marcus