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: 
cfountain72
Creator II
Creator II

Alias Table

Hello,

I am trying to create an app that shows # tickets opened and # tickets closed on a given date. I have a single Ticket table that lists every ticket, including its Submit Date and (if it's Closed) a Resolve Date. I also have a Date Dimension table. At its simplest, I would need to show (e.g.)

'On 12/10/2018, Submitted: 300 and Closed: 320 tickets', which would then be part of a larger line chart. 

I've tried creating a duplicate table, then using the DateDim table to join a date to the Submit Date of one, and to the Resolve Date pf the second table, but I'm getting a 'Duplicate Derived Field' error.:

Duplicate Derived Field: DERIVE FIELDS FROM FIELDS [DateValue-SubmissionDate], [SubmissionTS], [ModifiedTS], [SLAResponseTS], [SLAResponseDueTS], [SLAResolutionTS], [ResolutionDate], [SLAResolutionDueTS], [ETLLDDTM], [DateKey], [DisplayString], [LastFridayDate], [MonthEndDate], [TomorrowDate], [ClosedMasterTicketList.SubmissionTS], [ClosedMasterTicketList.DateValue-SubmissionDate], [ClosedMasterTicketList.ModifiedTS], [ClosedMasterTicketList.SLAResponseTS], [ClosedMasterTicketList.SLAResponseDueTS], [ClosedMasterTicketList.SLAResolutionTS], [ClosedMasterTicketList.SLAResolutionDueTS], [ClosedMasterTicketList.ETLLDDTM] USING [autoCalendar]

There are dup field names, but they are qualified by the duplicate table's name (ClosedMasterTicketList). Any suggestions?

Thanks in advance.

 

Labels (2)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You don't need to duplicate the data.  Instead, link both SubmitDate and ResolveDate to a canonical date through a link table.  Download the Qlik Sense version of this tutorial for an example:

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

View solution in original post

5 Replies
cfountain72
Creator II
Creator II
Author

This is an example of what I'm trying to accomplish...This is an example of what I'm trying to accomplish...

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You don't need to duplicate the data.  Instead, link both SubmitDate and ResolveDate to a canonical date through a link table.  Download the Qlik Sense version of this tutorial for an example:

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

cfountain72
Creator II
Creator II
Author

Thanks for the help! This looks like an excellent start.

ps. In these examples, there are Order, Shipment, and Invoice tables. Would I be able to use the single table to generate separate Submitted and Resolved Fact Calendars as well? That will be a great option for users.

Thanks again,
Chris
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Yes, this will work for multiple dates in the same fact table.  for the canonical (common) date, Load from the fact table multiple times, once for each date type. 

DateLink:
LOAD
TicketId // Fact Key
,OpenDate as Date // Fact Date
,'Open' as DateType // Fact Type
RESIDENT Tickets;

LOAD 
TicketId
,CloseDate as Date 
,'Close' as DateType
RESIDENT Tickets
;

For the separate calendars, generate using the individual date field -- OpenDate or CloseDate. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

 

cfountain72
Creator II
Creator II
Author

Excellent! Thanks again for your help Rob.