Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

etrotter
Contributor II

Duplication when creating Matching Dimension

In the script I am creating a variable that indicates if two fields match:


    If(

    Match([Encore.License_Number], [Workday.License_Number]),'Match Encore', 'Does Not Match')

        as Match.License_Number,

    If(

    Match([Encore.License_Class], [Workday.License_Class]),'Match Encore', 'Does Not Match')

        as Match.License_Class


The issue I am having is if there are multiple it doesn't match correctly:

Capture.PNG


Please let me know if you can help, thanks in advance!

1 Solution

Accepted Solutions
wdchristensen
Contributor III

Re: Duplication when creating Matching Dimension

If I were guessing, I would assume you joined the two on employee_id and you probably need to join the two tables on employee_id and License_Number. This way when you are comparing the two licenses you are NOT comparing an LPN license class to a drivers license class. Hope that helps. 

6 Replies
wdchristensen
Contributor III

Re: Duplication when creating Matching Dimension

Might try:

If( [Encore.License_Class] = [Workday.License_Class],'Match Encore', 'Does Not Match')

        as Match.License_Class

etrotter
Contributor II

Re: Duplication when creating Matching Dimension

that has the same result

wdchristensen
Contributor III

Re: Duplication when creating Matching Dimension

That likely means you have a many relationship where the "if" criteria is both True and False. Usually this means your join is missing one of the necessary keys. So should you expect to see true if it has one or more matches?

wdchristensen
Contributor III

Re: Duplication when creating Matching Dimension

If I were guessing, I would assume you joined the two on employee_id and you probably need to join the two tables on employee_id and License_Number. This way when you are comparing the two licenses you are NOT comparing an LPN license class to a drivers license class. Hope that helps. 

etrotter
Contributor II

Re: Duplication when creating Matching Dimension

Here is the new script I created:


NoConcatenate

Class_Temp:

Load

[WDE.W_CODE],

[Encore.W_CODE]&'|'&[Workday.License_Class] as %ClassKey,

Workday.License_Class

RESIDENT Workday;




Left join (Class_Temp)

Load

[Encore.W_CODE]&'|'&[Encore.License_Class] as %ClassKey,

    Encore.License_Class

Resident Encore;


//Left Join (Workday)

Class:

Load *,

If(

    Match([Encore.License_Class], [Workday.License_Class]),'Match Encore', 'Does Not Match')

        as Match.License_Class

   

Resident Class_Temp;



Drop table Class_Temp;


Drop Fields

[Encore.License_Class],

[Workday.License_Class]

From Class;



But it has this output:


Capture.PNG

etrotter
Contributor II

Re: Duplication when creating Matching Dimension

I figured it out, it was because I was pulling them from the original table instead of the new table I created.

Community Browser