Consolidating/matching fields

[haymarketpaul]

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_Title	DQS_Job_Title	DQS_Job_Title_Other	Consolidated_Job_Titles
Title1	Title2	Title3	Title1;Title2;Title3
Title1	Title1	Title2	Title1;Title2
Title1	Title2	Title1	Title1;Title2
Title1	Title2	Title2	Title1;Title2
Title1	Title1	Title1	Title1
Title1		Title3	Title1;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

[haymarketpaul · Accepted solution]

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

[johnw]

I think this would work:

concat(distinct pick(valueloop(1,3),Job_Title,DQS_Job_Title,DQS_Job_Title_Other),';')

Edit:  In a chart, anyway.  I don't think it would work in script.  If not, maybe this:

mid(if(len(Job_Title)

      ,';'&Job_Title)

   &if(len(DQS_Job_Title)

   and DQS_Job_Title<>Job_Title

      ,';'&DQS_Job_Title)

   &if(len(DQS_Job_Title_Other)

   and not match(DQS_Job_Title_Other,DQS_Job_Title,Job_Title)

      ,';'&DQS_Job_Title_Other),2)

[rwunderlich]

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

]

;

[haymarketpaul]

Thanks for the replies guys

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

[haymarketpaul]

This is great John - works perfectly, very clever