Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
swatitomar
Contributor III
Contributor III

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

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 IDCreated DateResolved DateBreached
A112/09/201621/10/2016Yes
A216/10/201630/11/2016NO
A317/10/201618/10/2016NO

Thanks in advance Swati

6 Replies
Anil_Babu_Samineni

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vinieme12
Champion II
Champion II

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

swatitomar
Contributor III
Contributor III
Author

Thanks Anil..

We don't need master calendar in this ??

N it will duplicate the entries ??

swatitomar
Contributor III
Contributor III
Author

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

Thanks .

Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
petter
MVP
MVP

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:

  1. 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.
  2. 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
  3. 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
  4. 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.