Creating a new table in the editor

Fri, 17 Jun 2016 08:41:33 GMT

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!

Re: Creating a new table in the editor

marcus_sommer — Fri, 17 Jun 2016 12:02:05 GMT

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

topic Re: Creating a new table in the editor in App Development

Creating a new table in the editor

Re: Creating a new table in the editor