4 Replies Latest reply: Dec 14, 2016 9:52 AM by Crystle Stamper RSS

    Compare two fields and replace certain values

    Crystle Stamper

      I have a table with Customers and Customer IDs. The problem is that about 4 or 5 of these Customers are listed in a dataset with the parent Customer and we need to break it up by state and replace the Parent Customer ID with the Child Customer ID based on the state.

       

      Example of Child-Parent relationship

      Parent Customer IDParent Customer StateChild Customer IDChild Customer State
      1233Georgia1534Georgia
      1233California1635California
      1233Maine1936Maine

       

       

      This is only correct for a select number of states, so I created an Excel file that has the ParentID, the State and the New Child ID I want to change the Parent ID to for those specific customers.

       

      I believe I know the way to fix this, but I keep getting weird results.

       

      I create a key in the First table and the Fix Excel document, that shows the ParentID and State. I use that key to join the two tables and compare them, and if the Keys match then replace the key with the new key, if not, then keep the original.

       

      I'm not sure it is doing this in the most efficient way though.

       

      If anyone else has had this issue and has any ideas or tricks that worked for them, any help would be greatly appreciated! Thanks!

        • Re: Compare two fields and replace certain values
          Sunny Talwar

          Can you share the script/sample to show what you are doing?

            • Re: Compare two fields and replace certain values
              Crystle Stamper

               

              There is a lot of script that goes before this to get the list of original customers, but here is the section that adds the keys and then compares them.

               

               

              //*************************************************************************
              // add key to main table
              //*************************************************************************
              SalesTrans2:
              LOAD *,
              STATE_ABBREV &'-'& CU_PARENTBP as DealerKey_Org
              Resident SalesTrans_Units
              ;

              DROP Table SalesTrans_Units;

               

              //*************************************************************************
              //Add key from Dealer Fix Excel document
              //*************************************************************************

              Left Join (SalesTrans2)
              LOAD StateName as FIX_StateName,
              StateID as FIX_StateID,
              DISTRIBUTOR_ID as OLD_DealerID,
              DISTRIBUTOR_ID as CU_PARENTBP,
              DealerID as NEW_DealerID,
              StateID & '-'& DISTRIBUTOR_ID as DealerKey_NewFix
              FROM
              POLK_MAP_DealerFix.xlsx
              (
              ooxml, embedded labels, table is BossUnits);

               

              //*************************************************************************
              //Compare and replace dealers based on the Dealer Fix excel doc
              //*************************************************************************
              SGM_2:
              LOAD *,
              if(DealerKey_NewFix = DealerKey_Org, NEW_DealerID, ORG_DealerID) as DealerID_FINAL,
              if(DealerKey_NewFix = DealerKey_Org, DealerKey_NewFix, DealerKey_Org) as DealerKey_FINAL,
              if(DealerKey_NewFix = DealerKey_Org, FIX_StateID, STATE_ABBREV) as StateID_FINAL

              Resident SalesTrans2;

              DROP Table SalesTrans2;

               

               

            • Re: Compare two fields and replace certain values
              Marco Wedel

              Can you describe your expected result given your provided sample data?

               

              thanks

               

              regards

               

              Marco

              • Re: Compare two fields and replace certain values
                Crystle Stamper

                I found out what the issue was. I'll try to explain as clearly as I can, in case someone else has this issue and comes across this post.

                 

                In my code I had a list of Dealers, all the Parent Dealers. But for about 5 Dealers, who showed up with different Dealer States but the same DealerID, we needed to break these out and use the Child DealerID instead to be able to differentiate between them. To do this, I have an Excel document that I add a new field that only relates to the Full list by these 5 dealers.

                 

                So essentially I have a field with the full list of dealers, and I add a field with only the 5 dealers I need changed, by a key that connects them to the specific 5 dealers, and the rest of the records in that field are blank. This is where my problem was, because my formula did not address all the blank records in my new field.

                 

                I changed my formula. Instead of looking for where the new field matched one of the DealerIDs in my full field, I just checked when the record was blank. If the record is blank, use the Original DealerID, if the field is NOT blank, then use the new DealerID provided in the new field. This fixed my issues with the data.

                 

                Below is the final script I used in my document. Thanks!

                 

                //*************************************************************************

                // add key to main table now that state and customer have been joined/added

                //*************************************************************************

                SalesTrans2:

                LOAD *,

                  STATE_ABBREV &'-'& CU_PARENTBP as DealerKey_Org

                Resident SalesTrans_Units

                ;

                 

                 

                DROP Table SalesTrans_Units;

                 

                 

                //*************************************************************************

                //Add key from Dealer Fix Excel document

                //*************************************************************************

                Left Join (SalesTrans2)

                LOAD

                // StateName as FIX_StateName,

                //     StateID as FIX_StateID,

                //     DISTRIBUTOR_ID as OLD_DealerID,

                //     DealerID as NEW_DealerID,

                     StateID & '-'& DISTRIBUTOR_ID as DealerKey_Org,

                     StateID &'-'& DealerID as DealerKey_NewFix

                FROM

                [..\..\01. Repository\99. External\data\POLK_MAP_DealerFix.xlsx]

                (ooxml, embedded labels, table is BossUnits);

                 

                 

                DROP Fields CU_PARENTBP, STATE_ABBREV;

                 

                 

                //*************************************************************************

                //Compare and replace dealers based on the Dealer Fix excel doc

                //*************************************************************************

                SGM_2:

                LOAD *,

                  if(Len(trim(DealerKey_NewFix)) = 0,DealerKey_Org, DealerKey_NewFix) as DealerKey_FINAL

                Resident SalesTrans2;

                 

                 

                DROP Table SalesTrans2;

                 

                 

                DROP Fields DealerKey_Org, DealerKey_NewFix;