topic Re: Consolidating/matching fields in QlikView

Consolidating/matching fields

haymarketpaul — Tue, 06 Dec 2011 16:35:35 GMT

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

Re: Consolidating/matching fields

haymarketpaul — Wed, 07 Dec 2011 14:21:10 GMT

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

johnw — Wed, 07 Dec 2011 23:44:27 GMT

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)

Re: Consolidating/matching fields

rwunderlich — Thu, 08 Dec 2011 06:06:51 GMT

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

haymarketpaul — Thu, 08 Dec 2011 10:53:00 GMT

Thanks for the replies guys

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

Re: Consolidating/matching fields

haymarketpaul — Thu, 08 Dec 2011 11:37:50 GMT

This is great John - works perfectly, very clever