Duplication when creating Matching Dimension

[etrotter]

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:

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

[wdchristensen]

Might try:

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

        as Match.License_Class

[etrotter]

that has the same result

[wdchristensen]

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 · Accepted solution]

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]

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:

[etrotter]

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