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
