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
    Krunoslav Pap

    Hi,

    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:

     

    OFFICE:

    LOAD * INLINE [

        ID, NAME

        1, office_1

        2, office_2

        3, office_3

        4, office_4

        5, office_5

        6, office_6

    ];

     

     

    Office_Map_by_id:

    Mapping LOAD

        ID, NAME Resident OFFICE;

     

    Office_Map_by_name:

    Mapping LOAD

         NAME, ID Resident OFFICE;

     

    TEST:

    LOAD * INLINE [

        F1, F2

        , office_1

        2,

        3,

        , office_4

        , office_5

        1,

        6,

        , office_5

    ];

     

    TEST_RESULT:

    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;

     

    regards

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

     

    Load

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

     

     

     

    Hope this help,

    Anosh

    • 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!