Breaking a Table Join

Mon, 28 Sep 2015 10:47:37 GMT

I am interested in breaking the join that is currently arises for "VesselName" .

I have included attachment showing the table viewer and also included the respective script below. Your assictance would be greatelu appreciated

Thanks

Rohan

ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO SSL36D;

/*

* Head Fixtures

*

*/

SELECT DISTINCT VH.ID HeadFixtureID, vh.VesselName,

Oown.SLCode 'Owner', Oown.Country 'Owner Country'

From VMHead AS VH WITH (NOLOCK)

JOIN OpFix AS own WITH (NOLOCK) ON own.ID = VH.TCFixture

JOIN Organisation AS Oown WITH (NOLOCK) ON Oown.ID = isnull(own.seller, own.SelPrincipal);

/*

* Voyages and Ports

* Link to Head Fixtures using HeadFixtureID

* This could be split in to two tables.

*/

SELECT DISTINCT v.id VoyageID, vp.ID PortID, v.HeadFixture HeadFixtureID, V.VesselName, V.VoyageNo, V.Status,

VP.Sequence, VP.Port, vp2.Country 'Port Country',

VP.Arrived 'Arrived', VP.Sailed 'Departed', VP.ActivityDisplay 'Port Activity',

V.FixedBy, V.OperatedBy,

ISNULL(V.StartBallast, V.StartVoyageDate) StartVoyage,

Year(V.StartVoyageDate)AS Start_Voyage_Year,

Month(V.StartVoyageDate)AS Start_Voyage_Month,

ISNULL(V.RedeliveryDate, V.EndVoyageDate) EndVoyage,

V.Type VoyageType

FROM VMVoyage AS V WITH (NOLOCK)

JOIN VMVoyagePort AS VP WITH (NOLOCK) ON VP.Voyage = V.ID

JOIN VesselPort AS VP2 WITH (NOLOCK) ON vp2.name = vp.Port

WHERE vp.DeleteFlag IS NULL

AND V.Category = 'FIXED';

/*

* Port Agens (DA's)

* Link to Voyage-Ports using Port ID

*/

SELECT DISTINCT dis.VMVoyagePort PortID,

dis.InvCurrency 'DA Currency',

dis.InvAmount 'DA Amount',

CASE DIS.InvType

WHEN 3 THEN 'Proforma'

WHEN 4 THEN 'Final'

ELSE 'Other'

END 'DAType', o.SLCode 'DA Agent',

dis.InvDueDate 'DA DueDate'

FROM DAInvSummary AS DIS WITH (NOLOCK)

JOIN Organisation AS O WITH (NOLOCK) ON o.id = dis.Supplier

WHERE isnull(dis.Active, 1) = 1

AND dis.ReplacedByInvSummary IS NULL ;

/*

* Cargo and charges

* Link to Voyage-Ports via Voyage ID

* This could be split in two, which would avoid the ISNULL where clause and LOJ.

*/

SELECT DISTINCT v.id VoyageID,

OVC.Type 'Cargo', OVC.ActNomMax 'MT',

Ochtr.SLCode 'Charterer', Ochtr.Country 'Charterer Country',

oc.Type 'Freight Charge Type',

oc.Curr 'Freight Charge Currency',

oc.Charge 'Freight Charge Amount',

oc.ExpectedDue 'Freight Charge Due Date',

oc.Status 'Freight Charge Status'

FROM VMVoyage AS V WITH (NOLOCK)

JOIN VMVoyageFixture AS VVF WITH (NOLOCK) ON VVF.VMVoyageNo = V.ID

JOIN OpFix AS chtr WITH (NOLOCK) ON chtr.ID = vvf.OpsFixture

LEFT OUTER JOIN OpCharge AS OC WITH (NOLOCK) ON chtr.id = oc.Fixture

JOIN Organisation AS Ochtr WITH (NOLOCK) ON Ochtr.ID = chtr.Buyer

JOIN OpVOYCargo AS OVC WITH (NOLOCK) ON OVC.Fixture = chtr.ID

WHERE ISNULL(oc.charge,1) <> 0;

/*

* Invoices

* Link to Voyage via VoaygeID

*/

SELECT DISTINCT v.id VoyageID,

oih.InvoiceNo,

oih.Header,

oig.Curr,

oih.Type,

oih.DueDate,

oih.ExpectedDueDate,

oih.InvoiceTotal,

oih.ReceivedTotal,

oih.Settled,

oih.Paid,

oih.Reference,

oih.PaidDate,

o.SLCode

FROM VMVoyage AS V WITH (NOLOCK)

JOIN VMVoyageFixture AS VVF WITH (NOLOCK) ON VVF.VMVoyageNo = V.ID

JOIN OpInvoiceHeader AS OIH WITH (NOLOCK) ON OIH.Fixture = vvf.OpsFixture

JOIN OpInvoiceGroup AS OIG WITH (NOLOCK) ON OIG.Fixture = OIH.Fixture

JOIN Organisation AS O WITH (NOLOCK) ON o.id = oih.Invoicee;

/*

* Bunkers

* Link to voyages via VoyageID

* Link to ports via PortID

*/

SELECT v.ID VoyageID, vp.ID PortID,

V.VesselName, V.VoyageNo, VP.Port, VVPBL.StemmedDate, VVPBL.Quality, VVPBL.DeliveryDate, VP.Arrived, VVPBL.Lifted, vvpbl.LiftingType,

VVPBS.SupplierFullName, VVPBL.Gross, VVPBL.ApprovedFlag, VVPBL.Gross * VVPBL.Lifted TotalUSD

FROM VMVoyage AS V WITH (NOLOCK)

JOIN VMVoyagePort AS VP WITH (NOLOCK) ON VP.Voyage = V.ID

JOIN VMVoyagePortBunkerSupplier AS VVPBS WITH (NOLOCK) ON VVPBS.VMVoyagePort = vp.id

JOIN VMVoyagePortBunkerLifting AS VVPBL WITH (NOLOCK) ON VVPBS.id = VVPBL.VMVoyagePortBunkerSupplier

WHERE VVPBL.Gross > 0

AND VVPBL.LiftingType NOT LIKE '* Survey';

Re: Breaking a Table Join

Peter_Cammaert — Mon, 28 Sep 2015 10:58:21 GMT

Remove VesselName from all LOAD/SELECT statements where you have other means to identify the related Vessel (a single VoyageID usually uniquely identifies the Vessel involved)

The synthetic keys can be removed by converting field sets into complex keys. First check whether you really need all those field copies in multiple tables (see below).

Another suggestion for which you do not have to create complex keys: normalize your data model. Things like Port and PortId have a one-to-one relationship (VoyageID and the other related fields probably have the same relation) so remove Port from all tables except one.

Re: Breaking a Table Join

Mon, 28 Sep 2015 14:21:48 GMT

Peter

Thank you for the simplistic and effective solution

It worked a charm

Rohan

topic Re: Breaking a Table Join in QlikView

Breaking a Table Join

Re: Breaking a Table Join

Re: Breaking a Table Join