Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
haymarketpaul
Contributor 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
Highlighted
haymarketpaul
Contributor III

Re: Consolidating/matching fields

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
Highlighted
haymarketpaul
Contributor III

Re: Consolidating/matching fields

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

Highlighted
MVP
MVP

Re: Consolidating/matching fields

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)

Highlighted
MVP & Luminary
MVP & Luminary

Re: Consolidating/matching fields

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

]

;


Highlighted
haymarketpaul
Contributor III

Re: Consolidating/matching fields

Thanks for the replies guys

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

Highlighted
haymarketpaul
Contributor III

Re: Consolidating/matching fields

This is great John - works perfectly, very clever