Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
haymarketpaul
Creator III
Creator III

Consolidating/matching fields

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

1 Solution

Accepted Solutions
haymarketpaul
Creator III
Creator III
Author

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

View solution in original post

5 Replies
haymarketpaul
Creator III
Creator III
Author

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
Champion III
Champion III

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III
Author

Thanks for the replies guys

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

haymarketpaul
Creator III
Creator III
Author

This is great John - works perfectly, very clever