Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Champion
Champion

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

1 Solution

Accepted Solutions

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

View solution in original post

4 Replies
Not applicable

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

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
Champion
Champion

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

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

View solution in original post