Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

How to manage two dates in one table

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

StateCity`ProjectContractorPlanned Start DatePlanned End DateCost
DeltaAsabaSupply of water packsChi PArk Systems 12/02/200513/05/20061000
LagosIkejaRepair of Computer SystemsBarry Bonds 01/04/201431/03/20153000
KanoKanoConstruction of Water DamSonia and Sisters03/03/201604/08/20184000

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:

StateCity`ProjectPlanned Start DateCost
DeltaAsabaSupply of water packs12/02/20051000
LagosIkejaRepair of Computer Systems01/04/20143000
KanoKanoConstruction of Water Dam03/03/20164000

StateCity`ContractorPlanned End DateCost
DeltaAsabaChi PArk Systems 13/05/20061000
LagosIkejaBarry Bonds 31/03/20153000
KanoKanoSonia and Sisters04/08/20184000

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

4 Replies
marcus_sommer

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

- Marcus

shraddha_g
Partner - Master III
Partner - Master III

you can try 2 ways -

1. IntervalMatch - refer IntervalMatch

2.Canonical Date - refer Canonical Date

akpofureenughwu
Creator III
Creator III
Author

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

marcus_sommer

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