Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
cfountain72
New Contributor III

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.

 

Tags (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Alias Table

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

5 Replies
cfountain72
New Contributor III

Re: Alias Table

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

 

MVP & Luminary
MVP & Luminary

Re: Alias Table

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
New Contributor III

Re: Alias Table

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
MVP & Luminary
MVP & Luminary

Re: Alias Table

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
New Contributor III

Re: Alias Table

Excellent! Thanks again for your help Rob.