Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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
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
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!