How to manage two dates in one table

akpofureenughwu — Thu, 08 Mar 2018 15:23:51 GMT

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

Re: How to manage two dates in one table

marcus_sommer — Thu, 08 Mar 2018 16:14:17 GMT

This kind of task is usually solved with an IntervalMatch to a master-calendar.

- Marcus

Re: How to manage two dates in one table

shraddha_g — Thu, 08 Mar 2018 16:19:39 GMT

you can try 2 ways -

1. IntervalMatch - refer IntervalMatch

2.Canonical Date - refer Canonical Date

Re: How to manage two dates in one table

akpofureenughwu — Thu, 08 Mar 2018 17:47:00 GMT

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

Re: How to manage two dates in one table

marcus_sommer — Fri, 09 Mar 2018 06:41:46 GMT

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

topic How to manage two dates in one table in QlikView

How to manage two dates in one table

Re: How to manage two dates in one table

Re: How to manage two dates in one table

Re: How to manage two dates in one table

Re: How to manage two dates in one table