Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
pascaldijkshoor
Contributor

Create timeline from start and end date per dimension

I have got a table with the following fields and an example of some of the lines included: 

AssetNumber | Status | FromDate | ToDate
87003 | online | 01-01-19 | 24-05-19
87003 | offline | 25-05-19 | 09-07-19 (today's date)
87004 | online | 01-01-19 | 09-07-19 
87005 | online | 01-05-19 | 13-06-19
87005 | offline | 14-06-19 | 23-06-19
87005 | online | 24-06-19 | 09-07-19 

I want to convert this table to a table with a timeline with one datefield. So it should look like this: 

NewDateField | AssetNumber | Status

01-01-19 | 87003 | online
02-01-19 | 87003 | online
03-01-19 | 87003 | online
... ... ... 
24-05-19 | 87003 | online
25-05-19 | 87003 | offline
26-05-19 | 87003 | offline 

and so on, and this for every AssetNumber.

So the new table should contain for every AssetNumber a range of all the dates from the FromDate to the ToDate, together with the corresponding status.

Can anyone help me with this?

1 Solution

Accepted Solutions
Highlighted
kaanerisen
Contributor III

Re: Create timeline from start and end date per dimension

Hi pascaldijkshoor,

You can do it in two ways.

1. Keep data structure by using IntervalMatch and solve it in front-end.

// Data
SampleData:
load 
AssetNumber,Status,date(date#(FromDate,'DD-MM-YY')) as FromDate,date(date#(ToDate,'DD-MM-YY')) as ToDate
Inline [
AssetNumber,Status,FromDate,ToDate
87003,online,01-01-19,24-05-19
87003,offline,25-05-19,09-07-19
87004,online,01-01-19,09-07-19 
87005,online,01-05-19,13-06-19
87005,offline,14-06-19,23-06-19
87005,online,24-06-19,09-07-19
];

// Master Calendar

MinMax:
Load min("FromDate") as MinDate,
	 Max("ToDate") as MaxData
Resident SampleData;

let vMinDate=peek('MinDate',0,'MinMax');
let vMaxDate=peek('MaxData',0,'MinMax');

drop table MinMax;

TempCalendar:
load Date($(vMinDate)+IterNo()-1) as Date
AutoGenerate(1) while $(vMinDate)+IterNo()-1 <= $(vMaxDate);

MasterCalendar:
Load  
               Date AS "Date",  
               week(Date) As Date.Week,  
               Year(Date) As Date.Year,
               'Q'&ceil(month(Date)/3) as Date.Quarter,
               'Q'&ceil(month(Date)/3)&'-'& Year(Date) as Date.QuarterYear,
               Month(Date) As Date.Month,  
               Day(Date) As Date.Day,
               date(monthstart(Date), 'MMM YYYY') as Date.MonthYear
Resident TempCalendar  
Order By Date ASC; 

Drop Table TempCalendar;

// IntervalMatch

Inner Join IntervalMatch ( Date ) 
LOAD FromDate, ToDate
Resident SampleData;

Untitled1.png

2 - Create the output Table on script side by using a simple while loop.

Output:
Load
      AssetNumber as Out.AssetNumber,Status as Out.Status,
      Date( FromDate + IterNo() - 1 ) as Out.Date
      Resident SampleData
      While IterNo() <= ToDate - FromDate + 1 ;

Untitled2.png 

Untitled.png

You can choose the best way based on your need. You can also check the attached qvf.

Hope it helps...

View solution in original post

2 Replies
Highlighted
kaanerisen
Contributor III

Re: Create timeline from start and end date per dimension

Hi pascaldijkshoor,

You can do it in two ways.

1. Keep data structure by using IntervalMatch and solve it in front-end.

// Data
SampleData:
load 
AssetNumber,Status,date(date#(FromDate,'DD-MM-YY')) as FromDate,date(date#(ToDate,'DD-MM-YY')) as ToDate
Inline [
AssetNumber,Status,FromDate,ToDate
87003,online,01-01-19,24-05-19
87003,offline,25-05-19,09-07-19
87004,online,01-01-19,09-07-19 
87005,online,01-05-19,13-06-19
87005,offline,14-06-19,23-06-19
87005,online,24-06-19,09-07-19
];

// Master Calendar

MinMax:
Load min("FromDate") as MinDate,
	 Max("ToDate") as MaxData
Resident SampleData;

let vMinDate=peek('MinDate',0,'MinMax');
let vMaxDate=peek('MaxData',0,'MinMax');

drop table MinMax;

TempCalendar:
load Date($(vMinDate)+IterNo()-1) as Date
AutoGenerate(1) while $(vMinDate)+IterNo()-1 <= $(vMaxDate);

MasterCalendar:
Load  
               Date AS "Date",  
               week(Date) As Date.Week,  
               Year(Date) As Date.Year,
               'Q'&ceil(month(Date)/3) as Date.Quarter,
               'Q'&ceil(month(Date)/3)&'-'& Year(Date) as Date.QuarterYear,
               Month(Date) As Date.Month,  
               Day(Date) As Date.Day,
               date(monthstart(Date), 'MMM YYYY') as Date.MonthYear
Resident TempCalendar  
Order By Date ASC; 

Drop Table TempCalendar;

// IntervalMatch

Inner Join IntervalMatch ( Date ) 
LOAD FromDate, ToDate
Resident SampleData;

Untitled1.png

2 - Create the output Table on script side by using a simple while loop.

Output:
Load
      AssetNumber as Out.AssetNumber,Status as Out.Status,
      Date( FromDate + IterNo() - 1 ) as Out.Date
      Resident SampleData
      While IterNo() <= ToDate - FromDate + 1 ;

Untitled2.png 

Untitled.png

You can choose the best way based on your need. You can also check the attached qvf.

Hope it helps...

View solution in original post

pascaldijkshoor
Contributor

Re: Create timeline from start and end date per dimension

The scripts work perfectly. Thank you very much!