Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a table that has more than one date and there is a need to merge two dates to get one date for time analysis
Here is a sample table
State | City` | Project | Contractor | Planned Start Date | Planned End Date | Cost |
---|---|---|---|---|---|---|
Delta | Asaba | Supply of water packs | Chi PArk Systems | 12/02/2005 | 13/05/2006 | 1000 |
Lagos | Ikeja | Repair of Computer Systems | Barry Bonds | 01/04/2014 | 31/03/2015 | 3000 |
Kano | Kano | Construction of Water Dam | Sonia and Sisters | 03/03/2016 | 04/08/2018 | 4000 |
Desired output:
Since we have two key dates on this dataset, I would love that have one date (master date) and still retain the planned start date and planned end date fields.
Here is my approach.
I don't know how to merge two dates from one table however I know how to merge two dates from two dates.. I split the main table into two parts
Start Table:
State | City` | Project | Planned Start Date | Cost |
---|---|---|---|---|
Delta | Asaba | Supply of water packs | 12/02/2005 | 1000 |
Lagos | Ikeja | Repair of Computer Systems | 01/04/2014 | 3000 |
Kano | Kano | Construction of Water Dam | 03/03/2016 | 4000 |
State | City` | Contractor | Planned End Date | Cost |
---|---|---|---|---|
Delta | Asaba | Chi PArk Systems | 13/05/2006 | 1000 |
Lagos | Ikeja | Barry Bonds | 31/03/2015 | 3000 |
Kano | Kano | Sonia and Sisters | 04/08/2018 | 4000 |
Start: //load the first table
LOAD RowNo() as StartKey,
State,
City,
Project,
[Planned start Date]
Cost
State & '_' &[Planned Start Date] as %Link
FROM
source
End: //load the second table
LOAD RowNo () as EndKey,
State,
City,
Project
[Planned End Date],
[ Cost,
Contractor,
State & '_' &[Planned End Date] as %Link
FROM
source
LINK: //create a control table (building a link between the two tables
LOAD DISTINCT
[Planned Start Date] as %Date,
State,
%Link
RESIDENT Start;
CONCATENATE (LINK)
LOAD DISTINCT
[Planned End Date] as %Date,
State,
%Link
RESIDENT End;
DROP FIELDS [Planned Start Date], StateFROM Start;
DROP FIELDS [Planned End Date], State FROM End;
MinMaxDates:
LOAD
min(num(fieldvalue('%Date',recno())))-1 as mindate,
max(num(fieldvalue('%Date',recno()))) as maxdate
AUTOGENERATE
fieldvaluecount('%Date');
LET vCalendarStart = num(peek('mindate', -1, 'MinMaxDates'));
LET vCalendarEnd = num(peek('maxdate', -1, 'MinMaxDates'));
DROP TABLE MinMaxDates;
Calendar: // create master date calendar
Load
TempDate AS %Date,
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Date(MonthStart(TempDate), 'YYYY-MMM') as YearMonth,
Month(TempDate) As Month,
Dual(WeekYear(TempDate) & '-' & Right('0' & Week(weekstart(TempDate)), 2), Floor(WeekStart(TempDate))) as YearWeek,
Day(TempDate) As Day,
WeekDay(TempDate) as WeekDay
;
LOAD
$(vCalendarStart) + Iterno() As Num,
Date($(vCalendarStart) + IterNo()) as TempDate,
IterNo() as DayCounter
AutoGenerate 1
While $(vCalendarStart) + IterNo() <= $(vCalendarEnd);
With the above script, I created the master date calendar but the app is not fully optimized and deep within me, I know this is far from the best. I don't like the part where I have to split the main table.
Can you help me review my script and provide your suggestions?
Thank you for your time
Regards
This kind of task is usually solved with an IntervalMatch to a master-calendar.
- Marcus
Thank you for your input marcus_sommer In the IntervalMatch scenario, two tables were used.
In my case, both dates are on the same table
Any clue please
I think both scenarios are the same - from and to within one table which are then related to another table which might be a fact-table or better a dimension-table like the master-calendar. In the classical way it creates a link-table with a synthetic key but this could be resolved if you like it.
- Marcus