4 Replies Latest reply: May 2, 2012 3:18 PM by Jonathan Dienst RSS

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

    vikas mahajan

      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

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

          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

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

            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

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

                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

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

                    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