5 Replies Latest reply: May 17, 2012 11:11 AM by Anosh Nathaniel RSS

    ApplyMap or Join Only If Null

    Scott Springer

      How do I conditionally apply a map or join?


      I have 2 tables, 1 is my master data which has 2 columns - 1 with an OfficeID and 1 with OfficeName.  Sometimes OfficeID is populated and other times OfficeName.  I have a second table (link table) that has the list of OfficeID's and corresponding names.  I want to apply a map or populate all of the NULL OfficeName records with the information from the second table.  What is the best way to accomplish this?


      Thank you!

        • Re: ApplyMap or Join Only If Null


          can office names be the same for different ids?


          don't know is this the best way, but you can solve it with two mapping tables:



          LOAD * INLINE [

              ID, NAME

              1, office_1

              2, office_2

              3, office_3

              4, office_4

              5, office_5

              6, office_6





          Mapping LOAD

              ID, NAME Resident OFFICE;



          Mapping LOAD

               NAME, ID Resident OFFICE;



          LOAD * INLINE [

              F1, F2

              , office_1



              , office_4

              , office_5



              , office_5




          LOAD if (IsNull(F1) or trim(F1)='', ApplyMap('Office_Map_by_name', F2, null()), F1) as result_id,

                 if(IsNull(F2) or trim(F2)='', ApplyMap('Office_Map_by_id', F1, null()), F2) as result_name

              Resident TEST;



          • ApplyMap or Join Only If Null
            Anosh Nathaniel

            Hi sspringer,


            Use isnull() clause to check where the office name is null and then use applymap to map it with office names as shown below in the load statement:



            if(isnull(office name)=-1, applymap())




            Hope this help,


              • ApplyMap or Join Only If Null
                Scott Springer

                I'm still not sure this solution works.  I tried this if(isnull(office name)=-1, applymap()) , however, if I use this and alias it as OfficeName, then I cannot also load OfficeName in the script above for rows that already have an OfficeName.  The problem is this:  I have a table with 20 rows, 10 rows already have OfficeName, but no OfficeID (those are fine).  10 rows have OfficeID but no OfficeName - I need to fill in those OfficeName's using a different table.

                Any more ideas?  Thank you so much for responding!