topic Re: Create timeline from start and end date per dimension in App Development

Create timeline from start and end date per dimension

pascaldijkshoor — Tue, 09 Jul 2019 10:14:24 GMT

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?

Re: Create timeline from start and end date per dimension

kaanerisen — Tue, 09 Jul 2019 11:07:18 GMT

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;

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 ;

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

Hope it helps...

Re: Create timeline from start and end date per dimension

pascaldijkshoor — Wed, 10 Jul 2019 07:59:48 GMT

The scripts work perfectly. Thank you very much!