topic Duplication when creating Matching Dimension in App Development

Duplication when creating Matching Dimension

etrotter — Tue, 10 Apr 2018 16:02:30 GMT

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!

Re: Duplication when creating Matching Dimension

wdchristensen — Tue, 10 Apr 2018 16:08:57 GMT

Might try:

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

as Match.License_Class

Re: Duplication when creating Matching Dimension

etrotter — Tue, 10 Apr 2018 16:16:18 GMT

that has the same result

Re: Duplication when creating Matching Dimension

wdchristensen — Tue, 10 Apr 2018 16:23:43 GMT

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?

Re: Duplication when creating Matching Dimension

wdchristensen — Tue, 10 Apr 2018 16:28:28 GMT

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.

Re: Duplication when creating Matching Dimension

etrotter — Tue, 10 Apr 2018 17:01:38 GMT

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:

Re: Duplication when creating Matching Dimension

etrotter — Tue, 10 Apr 2018 17:16:59 GMT

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