Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vikasmahajan
Not applicable

Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

table a

-----------

product code

batch_no

expiry_date

cfa_code

table b

------------

cfa_code

agency_type

I have this two tables loaded using sql select in Qlikview now I want to join this tables with common field cfa_code

how do I joins using left join in qlikview.

Thanks in advance for Help

Vikas Mahajan

Tags (3)
1 Solution

Accepted Solutions
jontydkpi
Not applicable

Re: Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

Hi

As I said in my previous post, you may not need to explicitly join these tables, but here goes:

Directory;

DIM_ProductBatch_Master:

LOAD "ProductBatch_Code",

    "Product_Code" AS B_Product_Code,

    "BM_PRODUCT_BATCHNO",

    BM_SOURCE_FACID as CFACODE,

    Date(Floor("Expiry_Date"),'DD/MM/YYYY') AS Expiry_Date

FROM

[Source QVDs\DIM_ProductBatch.qvd]

(qvd);

Join (DIM_ProductBatch_Master)

LOAD

    "CFA_Code" as CFACODE

    "CFA_Name",

    "State_Code",

    "Loc_SourceType";

    SQL SELECT

    "CFA_Code",

    "LOC_AGENCYPARENTID",

    "LOC_AGENCYID",

    "CFA_Name",

    "State_Code",

    "LOC_ACTIVE",

    "LOC_CUSTTYPE",

    "Loc_SourceType"

FROM "QLIKVIEW_DB".dbo."DIM_QV_CFAMaster"

WHERE LOC_ACTIVE = 'Y';

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
Not applicable

Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

In Qlikview it automatically will join on common fields so in this case you can just do:

TableFinal:

LOAD *

FROM table a

LEFT JOIN

LOAD *

FROM table b

Or something to that extent and it should join on the cfa_code.

Hope this helps,

Brandon

jontydkpi
Not applicable

Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

Hi

You may not need to perform a join in your example. The two tables will automatically be associated on cfa_code and selections on cfa_code and/or agency_type will filter the records in table a.

A join in Qlikview between these two tables will add the field agency_type to table a, to collapse your data structure to one table:

TableA:

SQL SELECT

     product code,

     batch_no,

     expiry_date,

     cfa_code

FROM tablea;

JOIN (TableA)

SQL SELECT

     cfa_code,

     agency_type

FROM tableb;

Hope that helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vikasmahajan
Not applicable

Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

Dear Sir,

I am loading  productbatch master from qvd and dim_cfa is other table i want to join this 2 tables how do I do it

Please reply me.

DIM_ProductBatch_Master:

Directory;
LOAD "ProductBatch_Code",
     "Product_Code" AS B_Product_Code,
     "BM_PRODUCT_BATCHNO",
      BM_SOURCE_FACID as CFACODE,
      Date(Floor("Expiry_Date"),'DD/MM/YYYY') AS Expiry_Date
FROM
[Source QVDs\DIM_ProductBatch.qvd]
(qvd);

and

DIM_CFA:

LOAD

"CFA_Code" as CFACODE

"CFA_Name",

"State_Code",

"Loc_SourceType";

SQL

SELECT

"CFA_Code",

"LOC_AGENCYPARENTID",

"LOC_AGENCYID",

"CFA_Name",

"State_Code",

"LOC_ACTIVE",

"LOC_CUSTTYPE",

"Loc_SourceType"

FROM

"QLIKVIEW_DB".dbo."DIM_QV_CFAMaster"

WHERE

LOC_ACTIVE =

'Y';

Thanks

jontydkpi
Not applicable

Re: Left joins in Qlikview (Select Sql & data is loaded after loading i want to join two tables

Hi

As I said in my previous post, you may not need to explicitly join these tables, but here goes:

Directory;

DIM_ProductBatch_Master:

LOAD "ProductBatch_Code",

    "Product_Code" AS B_Product_Code,

    "BM_PRODUCT_BATCHNO",

    BM_SOURCE_FACID as CFACODE,

    Date(Floor("Expiry_Date"),'DD/MM/YYYY') AS Expiry_Date

FROM

[Source QVDs\DIM_ProductBatch.qvd]

(qvd);

Join (DIM_ProductBatch_Master)

LOAD

    "CFA_Code" as CFACODE

    "CFA_Name",

    "State_Code",

    "Loc_SourceType";

    SQL SELECT

    "CFA_Code",

    "LOC_AGENCYPARENTID",

    "LOC_AGENCYID",

    "CFA_Name",

    "State_Code",

    "LOC_ACTIVE",

    "LOC_CUSTTYPE",

    "Loc_SourceType"

FROM "QLIKVIEW_DB".dbo."DIM_QV_CFAMaster"

WHERE LOC_ACTIVE = 'Y';

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein