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: 
vikasmahajan

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
jonathandienst
Partner - Champion III
Partner - Champion III

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