Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

Question on Joins

I have the following Script which runs fine, but I am not receiving the expected output of all columns having data. Is the structure incorrect?

sabrTicketing:

Select

  Event_Code        as    sabrEVENTCDE,

  Event_Desc              as    sabrEVENTDESC,

  Usage_Event_Date  as    sabrUSAGEEVENTDTE,  

(Noncomp_Sale_Qty + Noncomp_Return_Qty)      as    sabrSOLDQTY,

(Comp_Sale_Qty + Comp_Return_Qty)          as    sabrCOMPQTY,

(Noncomp_Sale_Qty + Noncomp_Return_Qty + Comp_Sale_Qty + Comp_Return_Qty)  as    sabrTTLQTY,

(Sale_Amount + Return_Amount) as    sabrREV

From

      (Select

      Event.Display_Order

      , Event.Event_Code as Event_Code

      , Event.Description as Event_Desc

      , To_Char (Uevent.Event_Date, 'MM/DD/YYYY') As Usage_Event_Date

      , Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')

         Then Ticket.Ticket_Id End) As Noncomp_Sale_Qty

      , Count (Case When Ticket.Price <> 0 And Oli.Transaction_Type_Code In ('RT','ER')

         Then Ticket.Ticket_Id End) * -1 As Noncomp_Return_Qty

      , Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('SA','ES','CS')

         Then Ticket.Ticket_Id End) As Comp_Sale_Qty

      , Count (Case When Ticket.Price = 0 And Oli.Transaction_Type_Code In ('RT','ER')

         Then Ticket.Ticket_Id End) * -1 As Comp_Return_Qty

      , Nvl (Sum (Case When Oli.Transaction_Type_Code In ('SA','ES','CS')

         Then Ticket.Price End),0.00) As Sale_Amount

      , Nvl (Sum (Case When Oli.Transaction_Type_Code In ('RT','ER')

         Then Ticket.Price End),0.00) * -1 As Return_Amount

      From hsbcr.Ticket Ticket

      Inner Join hsbcr.Order_Line_Item Oli

      On Ticket.Order_Line_Item_Id = Oli.Order_Line_Item_Id

      Or Ticket.Remove_Order_Line_Item_Id = Oli.Order_Line_Item_Id

      Inner Join HSBCR.Event Event

      On Oli.Event_Id = Event.Event_Id

      Inner Join hsbcr.Event Uevent --Different alias to distinguish the usage event.

      On Oli.Usage_Event_Id = Uevent.Event_Id

      Inner Join HSBCR.Order_Transaction Ot

      On Oli.Order_Id = Ot.Order_Id And Oli.Transaction_Id = Ot.Transaction_Id 

      Where Oli.Market_Type_Code = 'P' 

      And Nvl(Ot.Order_Trxn_Assoc_Type_Code,'X') <> 'SI'

      AND event.event_id >= '5039' and event.event_id <= '5079'

     

      Group By Event.Display_Order

      , Event.Event_Code

      , Event.Description

      , Uevent.Event_Date

      , event.event_id

      Order By event.event_id, Event.Display_Order, Uevent.Event_Date, Event.Event_Code);

  LEFT JOIN (sabrTicketing)

  

sabrProjections:

  LOAD

  eventDATE             AS SABRUSAGEEVENTDTE,

  [Event Code]           AS      sabrEVENTCDE,

  projectedSales        AS sabrPROJSALES,

     projREV                AS sabrPROJREV,

     projCOMP             AS sabrPROJCOMP,

     adjPROJSALES    AS sabrADJSALES,

     adjPROJCOMP     AS sabrADJCOMP,

     adjPROJREV        AS sabrADJREV

    

    FROM (ooxml, embedded labels, table is Sheet1);

   

    LEFT JOIN (sabrTicketing)

   

SabresTicketingNew:

  LOAD

  SABREVENTCDE,

  SABREVENTDESC,

  SABRUSAGEEVENTDTE,

  SABRSOLDQTY,

  SABRCOMPQTY,

  SABRTTLQTY,

SABRREV,

sabrPROJSALES,

      sabrPROJREV,

      sabrPROJCOMP,

      sabrADJSALES,

      sabrADJCOMP,

      sabrADJREV,

  'sabr' AS brandNME

  RESIDENT sabrTicketing;

  DROP TABLE sabrTicketing;

1 Solution

Accepted Solutions
MVP
MVP

I meant linking as a step in debugging process. I assume your key values are not matching, for example dates vs. timestamps.

View solution in original post

9 Replies
Specialist
Specialist

More specifically, in the output table, SabresTicketingNew, the columns:

      sabrPROJSALES,

      sabrPROJREV,

      sabrPROJCOMP,

      sabrADJSALES,

      sabrADJCOMP,

      sabrADJREV,

are all displaying as '-'

MVP
MVP

Seems like you are using event code and date as key, right?

Try linking your tables instead of joining, then check the keys for matching values.

Specialist
Specialist

Stefan -

Linking is fine, however ultimately I am attempting to create one giant table through concatenation. I will be replicating this script for several other data sources

MVP
MVP

I meant linking as a step in debugging process. I assume your key values are not matching, for example dates vs. timestamps.

View solution in original post

Specialist
Specialist

I tried not joining and just linked on event code and there were no issues linking the table.

MVP
MVP

What about SABRUSAGEEVENTDTE?

Specialist
Specialist

Same result

MVP
MVP

So if you link the two tables by the two fields (the same time), you get a correct linkage?

Then the JOIN of the two tables should work, too.

Specialist
Specialist

I used SABREVENTCDE as the field to join and this seems to function