Master Dimension

[Ciara]

Hi All

I'm wondering if there is a way of creating a master dimension and linking it to a known field.

For example :

In my load statement, I am mapping Postal Codes to their known names from a dictionary

• ApplyMap('Map_PostalCode_Name', PostalCode, 'Unknown') as PostalCodeName

However, the field that captures the PostalCode is also a free text field (I also have other fields that might identify the location of the patient) so I want to create a master dimension that will give me all the PostalCodeNames that I have mapped and also those ones that I can identify using the WildMatch or Match functions.  My PostalCode dictionary has 57 entries and since I already have the name of these 57 codes it seems wasteful to use up space in my master dimension to map these again.

Here is an example

PostalCode from Patient Registration	Name that was loaded from statement
DUB	Dublin
KIL	Kildare
COR	Cork
GAL	Galway
GALWAY	Unknown
GALW	Unknown

I want to match the PostalCode GALWAY or GALW to the name Galway and have just one dimension that will give me all.

= If(WildMatch(PostalCode, '*GALW*'), 'Galway',  etc

Hope that makes sense.  

Is it even possible to do this?

Ciara

[DavidM]

Since we're talking about postal codes, is it possible to use just first three letters (i.e. there are no duplicates GAL is always Galway).

In that case I would go with Left([Postal Code],3) as [Postal Code]

Create the map using only three letters and that should do it

[Ciara]

Ahh but we're talking about end-user entry so it could be ANYTHING haha  

Also, PostalCode is just the name given to the field where they enter the County so really I'm looking for a way to amalgamate a known field with a wildcard match.

For instance we have a county called KILDARE and another called KILKENNY.  In the dictionary KIL is the mnemonic for KILDARE and KILK is mnemonic for KILKENNY.  So the user can enter KIL or KILDARE or Co. Kildare or Co. Kil or Co. Kilkenny or Kilkenny etc etc 

[DavidM · Accepted solution]

Ouch that is a tough one.

What I would do is: Keep the applymap as it, then make another load, which uses the value from applymap unless it is unknown. Then in for unknown I would use pick match in this sort of way:

IF(PostalCode='Unknown', Pick(Wildmatch(PostalCode, '*galw*,*kilk*,*kild*),'Galwick','Kilkenny','Kildale'),PostalCode) as PostalCode

In this case it will involve lot of manual work as freetext is always trouble, you need to select the value matching right (as low characters as possible). You could even skip applymap here but, that way you would need to create match for every value.

[Ciara]

Yeah theres a lot of 'massaging' of data that needs to be done but what you have suggested is definitely going to help.  Thanks so much David

Ciara