Question on Joins

evansabres — Tue, 23 Oct 2018 18:27:18 GMT

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;

Re: Question on Joins

evansabres — Tue, 23 Oct 2018 19:16:04 GMT

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

sabrPROJSALES,

sabrPROJREV,

sabrPROJCOMP,

sabrADJSALES,

sabrADJCOMP,

sabrADJREV,

are all displaying as '-'

Re: Question on Joins

swuehl — Tue, 23 Oct 2018 19:28:25 GMT

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.

Re: Question on Joins

evansabres — Tue, 23 Oct 2018 19:29:47 GMT

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

Re: Question on Joins

swuehl — Tue, 23 Oct 2018 19:36:19 GMT

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

Re: Question on Joins

evansabres — Tue, 23 Oct 2018 19:46:10 GMT

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

Re: Question on Joins

swuehl — Tue, 23 Oct 2018 19:48:11 GMT

What about SABRUSAGEEVENTDTE?

Re: Question on Joins

evansabres — Tue, 23 Oct 2018 19:49:04 GMT

Same result

Re: Question on Joins

swuehl — Tue, 23 Oct 2018 19:51:34 GMT

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.

Re: Question on Joins

evansabres — Tue, 23 Oct 2018 19:52:05 GMT

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

topic Re: Question on Joins in QlikView

Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins

Re: Question on Joins