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: 
pascaldijkshoor
Creator
Creator

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
Creator III
Creator III

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
Creator III
Creator III

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...

pascaldijkshoor
Creator
Creator
Author

The scripts work perfectly. Thank you very much!