Multiple dates in table

[agni_gold]

Hi Friends,

I have one table which having Incident details and it has multiple dates like

Open Date,

Resolved Date

Closed Date

Due Date

I am creating my calendar using Open date, but what happening here when i am clicking on Resolved Incident filter , this will not filter my resolved incident in selected period because calendar was prepared by Open date.

But my requirement is like calendar should independent and whatever i select whether open or resolved or closed or due , dashboard should filter by their respective date.

Please provide any idea for this.

[swuehl]

You can use a canonical date and link your master calendar to this field:

Canonical Date

[agni_gold]

actually , i am making calendar fields in the same table while loading.

[swuehl]

That's fine.

I suggest to change your script to create a DateLink table that links your ticket table to a master calendar table.

Here is another example / tutorial create by Rob Wunderlich:

Tutorial - Using Common Date Dimensions and Shared Calendars

that also shows that you can combine a canonical date with master calendar tables for each date field.

[agni_gold]

Thanks,

Incident:

LOAD

Date(Floor(Timestamp(Timestamp#([OPENED_AT],'DD-MMM-YY hh.mm.ss.[fff] TT'))),'DD-MMM-YYYY') AS [Open Date],

Date(Floor(Timestamp(Timestamp#(RESOLVED_AT,'DD-MMM-YY hh.mm.ss.[fff] TT'))),'DD-MMM-YYYY') AS [Resolved Date],

Date(Floor(Timestamp(Timestamp#(CLOSED_AT,'DD-MMM-YY hh.mm.ss.[fff] TT'))),'DD-MMM-YYYY') AS [Closed Date],

Date(Floor(Timestamp(Timestamp#(DUE_DATE,'DD-MMM-YY hh.mm.ss.[fff] TT'))),'DD-MMM-YYYY') AS [Due Date]

FROM

This is my load script , can you help me to create canonical dates.

[swuehl]

Have you already looked at the samples I've provided? It's not that complicated basically create a ticket ID or primary key in your tickets table, then create the Link table like

DateLink:

LOAD DISTINCT

          Key,

          [Open Date] as DateLink,

          'Open Date' as Type

RESIDENT TicketsTable;

CONCATENATE (DateLink)

LOAD Key,

          [Resolved Date] as DateLink,

          'Resolved Date' as Type

RESIDENT TicketsTable;

Repeat with all your date field from TicketsTable.

Then create a master calendar from DateLink field (I believe there is even code for doing that in Rob's tutorial)

The Master Calendar