Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Jean-Yves
New Contributor

Simple join of two resident tables

Hello,

I have 2 tables : Tickets and Metrics.

I am trying to perform a join of the 2 tables , in a destination table called : TicketsAndMetrics .

But at the end of the load process, Tickets and Metrics are deleted, and TicketsAndMetrics  does not appear.

Any help will be appreciated, thank you in advance.

/*********************/
/*       Ticket      */
/*********************/
Tickets:
Load
    AssigneeId,
    CatId,
    CreatedAt,
    Month(CreatedAt) as CreatedAtMonth,    
    Year(CreatedAt) as CreatedAtYear,
    RequesterId,
    Status,
    ZoneId & '|' & TicketId as ZoneTicketId,
    TicketId,
    UpdatedAt,
    Subject,
    Type;
SQL SELECT AssigneeId,
    CatId,
    CreatedAt,
    Month(CreatedAt) as CreatedAtMonth,    
    Year(CreatedAt) as CreatedAtYear,
    RequesterId,
    Status,
    ZoneId,
    TicketId,
    UpdatedAt,
    Subject,
    Type
FROM Zendesk.dbo.Tickets;


/*********************/
/*      Metrics      */
/*********************/
Metrics:
Load FirstResolutionTimeInMinutesBusiness,
    FirstResolutionTimeInMinutesCalendar,
    RequesterWaitTimeInMinutesBusiness,
    RequesterWaitTimeInMinutesCalendar,
    ZoneId & '|' & TicketId as ZoneTicketId,
    SolvedAt,
    AssignedAt,
    [OnHoldTimeInMinutesCalendar]
  where Exists(TicketId);
SQL SELECT FirstResolutionTimeInMinutesBusiness,
    FirstResolutionTimeInMinutesCalendar,
    RequesterWaitTimeInMinutesBusiness,
    RequesterWaitTimeInMinutesCalendar,
    ZoneId ,
     TicketId,
    SolvedAt,
    AssignedAt  ,
    [OnHoldTimeInMinutesCalendar]
FROM Zendesk.dbo.TicketMetrics;


/*****************************/
/*     TicketsAndMetrics     */
/*****************************/
TicketsAndMetrics:
LOAD AssigneeId,
    CatId,
    CreatedAt,
    Month(CreatedAt) as CreatedAtMonth,    
    Year(CreatedAt) as CreatedAtYear,
    RequesterId,
    Status,
    ZoneTicketId,
    TicketId,
    UpdatedAt,
    Subject,
    Type  Resident Tickets;
    
join
 LOAD FirstResolutionTimeInMinutesBusiness,
    FirstResolutionTimeInMinutesCalendar,
    RequesterWaitTimeInMinutesBusiness,
    RequesterWaitTimeInMinutesCalendar,
    ZoneTicketId,
    SolvedAt,
    AssignedAt,
    [OnHoldTimeInMinutesCalendar] Resident Metrics;
DROP Table Tickets;
DROP Table Metrics;

Labels (2)
1 Solution

Accepted Solutions

Re: Simple join of two resident tables

Hi, that looks like TicketsAndMetrics is being autoconcatenated in the previous table, to avoid this use 'NoConcatenate':
TicketsAndMetrics:
NoConcatenate LOAD AssigneeId,
...
3 Replies

Re: Simple join of two resident tables

Hi, that looks like TicketsAndMetrics is being autoconcatenated in the previous table, to avoid this use 'NoConcatenate':
TicketsAndMetrics:
NoConcatenate LOAD AssigneeId,
...
Jean-Yves
New Contributor

Re: Simple join of two resident tables

Thank you very much, Ruben.

Re: Simple join of two resident tables

Hi, I'm not sure if you still are having problems viewing Metrics data... default join is outer, so the fields should be there even if there is no relation between both tables, when reloading confirm that Metrics table is loading records.

If there are records try removing the drop to check same values in field ZoneTicketId trying to look for a hint on what kind of relationships exists.