Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

Left join excel source on oracle source

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

1 Reply
andrey_krylov
Specialist
Specialist

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);