topic Re: Multiple dates in table in QlikView

Multiple dates in table

agni_gold — Sun, 29 May 2016 22:16:59 GMT

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.

Re: Multiple dates in table

swuehl — Sun, 29 May 2016 22:20:14 GMT

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

Canonical Date

Re: Multiple dates in table

agni_gold — Sun, 29 May 2016 22:22:07 GMT

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

Re: Multiple dates in table

swuehl — Sun, 29 May 2016 22:33:25 GMT

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.

Re: Multiple dates in table

agni_gold — Sun, 29 May 2016 23:10:10 GMT

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.

Re: Multiple dates in table

swuehl — Sun, 29 May 2016 23:22:30 GMT

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