Left join excel source on oracle source - Qlik Community

pascaldijkshoor · ‎2018-11-01

Hi everyone,

I am currently using a main table from oracle. I need to add the connection costs from an excel table to this table. The connection costs are dependent on the columns connection and year (tsuitd). I was thinking of joining my main table and excel table with a left join if this is possible. Can someone help me with this?

Full script of the main table is shown below. The excel table I need to join with the main table looks like this:

ExcelTable:

LOAD

Connection,

"Year"

"Connection Costs",

FROM [lib://Data_Qlik/Connections\Connection Costs.xlsx]

(ooxml, embedded labels, table is Sheet1);

I'm currently using the following script for my oracle table:

LIB CONNECT TO 'Chainware11g';

MainTable:

LOAD cuactk as Key,

curem1 as LicensePlate,

afd as Branche,

gebied as Division,

cuasst as Assettype,

curem1 as Activity_Asset_Details,

jrasst as Asset_Number,

tsrido as Tripnumber_CW,

cuacty as Activity,

tsaasd as Start_Date,

tsaaed as End_Date,

tsdist as Activity_Distance,

tsuitd as Load_Date,

trip_loadindex,

shipment_loadindex,

shipment_loadindex * tsdist as Shipment_Productivity,

trip_loadindex * tsdist as Trip_Productivity,

zoek as Principal,

tsstts as CW_StatusCode,

dosvlg as Shipment_Number_Linked,

Left(dosvlg,3) AS Department_Code,

Connection,

year (tsuitd)

;

SELECT

cuacta. "cuasst",

cuacta. "curem1",

expafd. "afd",

expafd. "gebied",

cuacta. "jrasst",

cuacta. "cuactk",

cuactt. "cuactk",

cuactt. "cuconn" as "Connection",

cuacts. "cuactk",

cuactt. "cuacty",

cuactt. "tsaasd",

cuactt. "tsaaed",

--cuactt. "tsdist",

case

when cuactt. "tsdist" = 0

then 0.01

when cuactt. "tsdist" is null

then 0.01

else cuactt."tsdist"

end as "tsdist",

cuactt. "tsloai" as "trip_loadindex",

cuactt. "tsrido",

cuacts. "dosvlg",

tsdsmd. "dosvlg",

tsdsmd. "tsstts",

tsdsmd. "tsuitd",

tsdsmd. "tsloai" as "shipment_loadindex",

tsdnar. "dosvlg",

tsdnar. "zoek"

FROM "ICJDR"."cuf_cuactt" cuactt,

"cuf_cuacta" cuacta

left join "cef_wagens" wagens on

cuacta."jrasst" = wagens."autonr"

left join "cef_expafd" expafd on

wagens."afd" = expafd."afd",

"cuf_cuacts" cuacts,

"cef_tsdsmd" tsdsmd

left join "cef_tsdnar" tsdnar on

tsdsmd."dosvlg" = tsdnar."dosvlg"

WHERE

cuactt. "cuactk" = cuacta. "cuactk"

AND cuactt. "cuactk" = cuacts. "cuactk"

AND cuacts. "dosvlg" = tsdsmd. "dosvlg"

AND "cuasst" = 'driving unit'

--AND cuactt. "cuacty" = 'Driving'

AND ( cuactt. "cuacty" in ('Driving', 'Connection')

OR cuactt. "cuacty" in ( 'Mounting', 'Loading')

AND cuactt. "tsrido" not in (

SELECT

chktrp."tsrido"

FROM

"cuf_cuacts" chktrp

WHERE

chktrp."tsrido" = cuacts."tsrido"

and chktrp."dosvlg" = cuacts."dosvlg"

and chktrp."cuacty" = 'Driving'

))

AND tsdnar. "tsroln" = '0'

AND tsdsmd."tsuitd" >= to_date('01-01-2018', 'DD-MM-YYYY');

Thanks in advance

andrey_krylov · ‎2018-11-01

Hi Pascal. Here I see no difficulties, load the main table and then join the excel table. Just make sure that the first table has the same number of rows after joining and check the result if everything was connected fine

LIB CONNECT TO 'Chainware11g';

MainTable:

LOAD cuactk as Key,

curem1 as LicensePlate,

afd as Branche,

gebied as Division,

cuasst as Assettype,

curem1 as Activity_Asset_Details,

jrasst as Asset_Number,

tsrido as Tripnumber_CW,

cuacty as Activity,

tsaasd as Start_Date,

tsaaed as End_Date,

tsdist as Activity_Distance,

tsuitd as Load_Date,

trip_loadindex,

shipment_loadindex,

shipment_loadindex * tsdist as Shipment_Productivity,

trip_loadindex * tsdist as Trip_Productivity,

zoek as Principal,

tsstts as CW_StatusCode,

dosvlg as Shipment_Number_Linked,

Left(dosvlg,3) AS Department_Code,

Connection,

year (tsuitd)

;

SELECT

cuacta. "cuasst",

cuacta. "curem1",

expafd. "afd",

expafd. "gebied",

cuacta. "jrasst",

cuacta. "cuactk",

cuactt. "cuactk",

cuactt. "cuconn" as "Connection",

cuacts. "cuactk",

cuactt. "cuacty",

cuactt. "tsaasd",

cuactt. "tsaaed",

--cuactt. "tsdist",

case

when cuactt. "tsdist" = 0

then 0.01

when cuactt. "tsdist" is null

then 0.01

else cuactt."tsdist"

end as "tsdist",

cuactt. "tsloai" as "trip_loadindex",

cuactt. "tsrido",

cuacts. "dosvlg",

tsdsmd. "dosvlg",

tsdsmd. "tsstts",

tsdsmd. "tsuitd",

tsdsmd. "tsloai" as "shipment_loadindex",

tsdnar. "dosvlg",

tsdnar. "zoek"

FROM "ICJDR"."cuf_cuactt" cuactt,

"cuf_cuacta" cuacta

left join "cef_wagens" wagens on

cuacta."jrasst" = wagens."autonr"

left join "cef_expafd" expafd on

wagens."afd" = expafd."afd",

"cuf_cuacts" cuacts,

"cef_tsdsmd" tsdsmd

left join "cef_tsdnar" tsdnar on

tsdsmd."dosvlg" = tsdnar."dosvlg"

WHERE

cuactt. "cuactk" = cuacta. "cuactk"

AND cuactt. "cuactk" = cuacts. "cuactk"

AND cuacts. "dosvlg" = tsdsmd. "dosvlg"

AND "cuasst" = 'driving unit'

--AND cuactt. "cuacty" = 'Driving'

AND ( cuactt. "cuacty" in ('Driving', 'Connection')

OR cuactt. "cuacty" in ( 'Mounting', 'Loading')

AND cuactt. "tsrido" not in (

SELECT

chktrp."tsrido"

FROM

"cuf_cuacts" chktrp

WHERE

chktrp."tsrido" = cuacts."tsrido"

and chktrp."dosvlg" = cuacts."dosvlg"

and chktrp."cuacty" = 'Driving'

))

AND tsdnar. "tsroln" = '0'

AND tsdsmd."tsuitd" >= to_date('01-01-2018', 'DD-MM-YYYY');

Left join(MainTable)

LOAD

Connection,

"Year"

"Connection Costs",

FROM [lib://Data_Qlik/Connections\Connection Costs.xlsx]

(ooxml, embedded labels, table is Sheet1);