topic Re: Multiple date in single table and Master Calender, how to create tdata modelhe in QlikView

Multiple date in single table and Master Calender, how to create tdata modelhe

swatitomar — Sun, 19 Feb 2017 04:39:21 GMT

Hi All,

I have 1 table and multiple dates

Like created date , resolved date and want to chart that shows 1 bare on resolved date n 1 on created date or stacked chart .

There will be the need of calendar master but how that I not not sure .plz help me out

Request ID	Created Date	Resolved Date	Breached
A1	12/09/2016	21/10/2016	Yes
A2	16/10/2016	30/11/2016	NO
A3	17/10/2016	18/10/2016	NO

Thanks in advance Swati

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

Anil_Babu_Samineni — Sun, 19 Feb 2017 04:55:32 GMT

May be load some thing like below

LOAD [Request ID] as ReqID,

[Resolved Date] as CommonDate,

'Resolved Date' as Flag,

Breached

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD [Request ID] as ReqID,

[Created Date] as CommonDate,

'Created Date' as Flag,

Breached

FROM

(ooxml, embedded labels, table is Sheet1);

And then Create stacked chart using Dim as CommonDate and What ever metric and go ahead and do it.

Data model seems below

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

vinieme12 — Sun, 19 Feb 2017 05:20:45 GMT

Here is how, without duplicating the Fact rows

1) Split fact into three tables

1- request I'd and Breached

2-request I'd and Created date

3- Request I'd and Resolved date

Then proceed as below

Search Recipes | Qlikview Cookbook

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

swatitomar — Sun, 19 Feb 2017 16:10:01 GMT

Thanks Anil..

We don't need master calendar in this ??

N it will duplicate the entries ??

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

swatitomar — Sun, 19 Feb 2017 16:14:50 GMT

U mean create link table.. il try this also..

Thanks .

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

Anil_Babu_Samineni — Sun, 19 Feb 2017 16:54:53 GMT

I don't think so whether you need Master Calendar to Ctrl This?

Where you seen the Duplicated. I didn't found anything

It would better if you share Any Image by using Hand Sketch to look the same to illustrate the Output

Re: Multiple date in single table and Master Calender, how to create tdata modelhe

petter — Sun, 19 Feb 2017 19:41:48 GMT

With multiple dates you have something that is often called a role playing dimension. You date is a dimension that has different roles - both Created and Resolved.

You can implement this in several ways:

Keep it as it is without creating an "master calendar" or multiple "master calenders". Just do your date references to various parts with date-functions like Year(), Month() and so forth. This could lead to rather complicated expressions depending on the complexity of your application.
You can create two separate master calendars: one for created and one for resolved. It is quite easy to do in your load script as you just duplicate the first one after you created it. But keep in mind that field names for the two different calendars have to be different.
Why You sometimes should Load a Master Table several times
Create a link-table - this is what Henric Cronström have called a "Canonical Date". Then the link table keep track of the different roles that the dates are playing.
Canonical Date
A combination of two or more of the previous approaches

There are many discussions and articles about this on this community. The two documents above is essential reading well worth the time studying it throughly - it will save you countless of hours getting early exposure to best practice when it comes to date handling in the data model and your application.