Simple join of two resident tables

[Jean-Yves]

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;

[rubenmarin · Accepted solution]

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

[Jean-Yves]

Thank you very much, Ruben.

[rubenmarin]

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.