5 Replies Latest reply: Dec 8, 2011 6:37 AM by Paul Nockolds RSS

    Consolidating/matching fields

    Paul Nockolds

      Hi All

       

      I have three different Job Title fields that i need to consolidate into a single column.

       

      If any of the three columns have matching Job Titles then i only want to display that Job Title once in the consolidated column.

       

      So something similar to this...

      Job_TitleDQS_Job_TitleDQS_Job_Title_OtherConsolidated_Job_Titles
      Title1Title2Title3Title1;Title2;Title3
      Title1Title1Title2Title1;Title2
      Title1Title2Title1Title1;Title2
      Title1Title2Title2Title1;Title2
      Title1Title1Title1Title1
      Title1
      Title3Title1;Title3

       

      I'm not sure of the best way to compare each column with each other and then omit where necessary any columns that match each other.

       

      I've got the following that will append them together ok but am not sure how to handle if any 2 or more of the Job_Titles match each other

       

      The 3 fields are called:

                Job_Title

                DQS_Job_Title

                DQS_Job_Title_Other

       

         
      
       ,If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)>0,Job_Title & ';' & DQS_Job_Title & ';' & DQS_Job_Title_Other,
      
          If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)=0,Job_Title & ';' & DQS_Job_Title,
      
          If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)>0,Job_Title & ';' & DQS_Job_Title_Other,
      
          If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)=0,Job_Title,
      
          If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)>0,DQS_Job_Title & ';' & DQS_Job_Title_Other,
      
          If(Len(Job_Title)=0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)>0,DQS_Job_Title_Other,
      
          If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)=0,DQS_Job_Title,'')))))))
      
                                                                                                  AS Consolidated_Job_Titles
      

       

      Any ideas most welcome

        • Re: Consolidating/matching fields
          Paul Nockolds

          Problem solved...

           

           

              ,If(Job_Title<>DQS_Job_Title and Job_Title<>DQS_Job_Title_Other and DQS_Job_Title<>DQS_Job_Title_Other,
                  //Then append them together - checking for any blanks
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)>0,Job_Title & ';' & DQS_Job_Title & ';' & DQS_Job_Title_Other,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)=0,Job_Title & ';' & DQS_Job_Title,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)>0,Job_Title & ';' & DQS_Job_Title_Other,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)=0,Job_Title,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)>0,DQS_Job_Title & ';' & DQS_Job_Title_Other,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title)=0 and Len(DQS_Job_Title_Other)>0,DQS_Job_Title_Other,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0 and Len(DQS_Job_Title_Other)=0,DQS_Job_Title,''))))))),
          
              //If Job_Title=DQS_Job_Title only then append accordingly
              If(Job_Title=DQS_Job_Title and Job_Title<>DQS_Job_Title_Other and DQS_Job_Title<>DQS_Job_Title_Other,
          
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title_Other)>0,Job_Title & ';' & DQS_Job_Title_Other,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title_Other)=0,Job_Title,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title_Other)>0,DQS_Job_Title_Other,''))),
          
              //If Job_Title=DQS_Job_Title_Other only then append accordingly
              If(Job_Title<>DQS_Job_Title and Job_Title=DQS_Job_Title_Other and DQS_Job_Title<>DQS_Job_Title_Other,
          
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0,Job_Title & ';' & DQS_Job_Title,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0,Job_Title,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0,DQS_Job_Title,''))),
          
              //If DQS_Job_Title=DQS_Job_Title_Other only then append accordingly
              If(Job_Title<>DQS_Job_Title and Job_Title<>DQS_Job_Title_Other and DQS_Job_Title=DQS_Job_Title_Other,
          
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)>0,Job_Title & ';' & DQS_Job_Title,
                  If(Len(Job_Title)>0 and Len(DQS_Job_Title)=0,Job_Title,
                  If(Len(Job_Title)=0 and Len(DQS_Job_Title)>0,DQS_Job_Title,''))),
          
              //If ALL Job_Titles are the same then append accordingly
              If(Job_Title=DQS_Job_Title and Job_Title=DQS_Job_Title_Other and DQS_Job_Title=DQS_Job_Title_Other,
          
                  If(Len(Job_Title)>0,Job_Title,''),
          
              )))))
                                                                                                      AS Consolidated_Job_Titles
          
          • Re: Consolidating/matching fields
            Rob Wunderlich

            How about:

             

            data:

            LOAD

              RecId, Job_Title, DQS_Job_Title, DQS_Job_Title_Other,

              concat(DISTINCT SubfieldJob, ';') as Consolidated_Job_Titles

            WHERE len(SubfieldJob)>0

            GROUP BY RecId,Job_Title, DQS_Job_Title, DQS_Job_Title_Other

            ;

            LOAD *,

              SubField(

                Job_Title & ';' & DQS_Job_Title & ';' & DQS_Job_Title_Other

              , ';') as SubfieldJob

            ;

            LOAD *, RecNo() as RecId INLINE [

            Job_Title, DQS_Job_Title, DQS_Job_Title_Other

            Title1,Title2,Title3

            Title1,Title1,Title2

            Title1,Title2,Title1

            Title1,Title2,Title2

            Title1,Title1,Title1

            Title1,,Title3

            ]

            ;


            • Re: Consolidating/matching fields
              Paul Nockolds

              Thanks for the replies guys

               

              I'll investigate both of these options...very interesting