6 Replies Latest reply: Oct 12, 2016 2:03 AM by Nitin Jadhav RSS

    Load Master Records for Fact Data.

    Nitin Jadhav

      Hi,

       

      I have Transaction Table(employee leaves) and a Master table(employee master).

       

      I want to load employee master for which employee leave exists.

       

      Can someone give me syntax and code for this.

       

      Regards, Nitin

        • Re: Load Master Records for Fact Data.
          Phaneendra Kunche

          Load the Transaction Table first and then load your Employee Master table with WHERE EXIST command.

           

          Ex:

          Emploee_Transaction:

          Load Employee_ID, ..., .... From Emploee_Transaction.qvd;

           

          Emploee_Master:

          Employee_ID, NAME, CITY,etc... From Employee_Master

          where exists (Employee_ID);


          Assuming you have a key with similar name in both the tables (Employee_ID)

            • Re: Load Master Records for Fact Data.
              Nitin Jadhav

              Thanks for the answer, it solved my problem now I have another complexity in exists clause.

               

              Can I put two fields in exists clause? e.g.   where exists(EmpType,Employee_ID)

               

              Scenario: Emp-Master 1 - Permanent Employees - EmpType = 'P'

                              Emp-Master 2 - Subcontract Employees - EmpType = 'S'

               

              My Transaction table will be EmpTyp and EmpID

                • Re: Load Master Records for Fact Data.
                  Phaneendra Kunche

                  You can try these

                   

                  where exists(Employee_ID) and exists(EmpType)

                   

                  or you can create a Key like EmpType&'-'&Employee_ID as EMP_KEY in your transaction table load and use below where clause.

                  where exists(EmpType&'-'&Employee_ID)



                  if your data is not that big you can use LEFT KEEP instead of EXISTS.

                   

                  Ex:

                  Emploee_Transaction:

                  Load Employee_ID, ..., .... From Emploee_Transaction.qvd;

                   

                  Emploee_Master:

                  left keep (Emploee_Transaction)

                  Employee_ID, NAME, CITY,etc... From Employee_Master;



                    • Re: Load Master Records for Fact Data.
                      Nitin Jadhav

                      Thanks for reply,

                      I tried both option but it is not working.

                       

                      LOAD
                          TRN_DATE,
                          EMP_TYPE&'-'&EMP_ID as Key    //Composite key
                          FROM [lib://Trn\EMPTRN.QVD]
                      (qvd)
                      WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

                      EMPMST:
                      LOAD
                          SUBCON_CD,
                          SUBCON_NAME,
                          EMP_RANK,
                          EMP_TYPE&'-'&EMP_ID as Key    //Composite key
                      FROM [lib://Mst\EMPMST.QVD]
                      (qvd)
                      where exists(EMP_ID) and exists(EMP_TYPE);

                       

                      Here is the code.

                        • Re: Load Master Records for Fact Data.
                          Phaneendra Kunche

                          Try this.

                           

                           

                          EMPTRN:

                           

                          LOAD

                              TRN_DATE,

                              EMP_TYPE&'-'&EMP_ID as Key    //Composite key

                              FROM

                          (qvd)

                          WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

                           

                           

                           

                          EMPMST:

                          LOAD

                              SUBCON_CD,

                              SUBCON_NAME,

                              EMP_RANK,

                              EMP_TYPE&'-'&EMP_ID as Key    //Composite key

                          FROM

                          (qvd)

                          where exists(Key, EMP_TYPE&'-'&EMP_ID);

                           

                           

                           

                           

                           

                          //====================== Second option ================================

                           

                           

                          EMPTRN:

                           

                          LOAD

                              TRN_DATE,

                              EMP_TYPE&'-'&EMP_ID as Key    //Composite key

                              FROM

                          (qvd)

                          WHERE NUM(FLOOR(TRN_DATE))>=NUM(FLOOR(MAKEDATE(2016,10,1)));

                           

                           

                           

                          EMPMST:

                           

                          Left Keep (EMPTRN)

                           

                          LOAD

                              SUBCON_CD,

                              SUBCON_NAME,

                              EMP_RANK,

                              EMP_TYPE&'-'&EMP_ID as Key    //Composite key

                          FROM

                          (qvd)

                          where;