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: 
Not applicable

Creating a new table in the editor

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:

ApplicationID
App11
App22
App33

Lifecycle table:

AppIDTypeDate
1Start01.01.2013
1End01.01.2019
2Start09.04.2011
3End30.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:

YearAppID
20161
20162
20171

How can I create these tables, especially  the connection table, within the data editor?

I would appreciate your help!

1 Reply
marcus_sommer

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