1 Reply Latest reply: Jun 10, 2014 5:01 PM by Stefan Wühl RSS

    Loading from different places

    Ashley Navin

      So I want to load information from this excel file into a tmp table and group the information in that tmp table with the acct_nbr from another table. I tried left joining the new table to the old one but it increases the amount of rows i have and includes acct_nbr's that are not in the original 'LoanLoss' table.

      How do I link these two table by acct_nbr so that the only acct_nbr's that appear are in the LoanLoss table before i load the excel sheet in??

       

       

       

       

      Tmp:

      Left Join (LoanLoss)

      LOAD acct_nbr,

           UltimateOutcome

      FROM

      [C:\Users\anavin\Desktop\UltimateOutcome.xlsx]

      (ooxml, embedded labels, table is Sheet1);

        • Re: Loading from different places
          Stefan Wühl

          I think you shouldn't see new acct_nbr values in your joined table, when using a left join.

           

          I would recommend using a MAPPING LOAD approach if you just want to add the value for UltimateOutcome per acct_nbr in your LoanLoss table.

           

          MAP:

          MAPPING

          LOAD acct_nbr,

               UltimateOutcome

          FROM

          [C:\Users\anavin\Desktop\UltimateOutcome.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          LoadLoss:

          LOAD acct_nbr,

               applymap('MAP',acct_nbr, 'No Matching Mapping') as UltimateOutcome,

          ...

          FROM ....;

           

          If you are asking to load only the values for acct_nbr that are already in LoadLoss, look into EXISTS() function:

           

          Tmp:

          Left Join (LoanLoss)

          LOAD acct_nbr,

               UltimateOutcome

          FROM

          [C:\Users\anavin\Desktop\UltimateOutcome.xlsx]

          (ooxml, embedded labels, table is Sheet1)

          WHERE EXISTS(acct_nbr);