Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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...
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...
The scripts work perfectly. Thank you very much!