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

[vikasmahajan]

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

[#2]

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

[jonathandienst]

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

[vikasmahajan]

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

[jonathandienst · Accepted solution]

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