Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Ciara
Creator
Creator

Master Dimension

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 RegistrationName that was loaded from statement
DUBDublin
KILKildare
CORCork
GALGalway
GALWAYUnknown
GALWUnknown

 

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

 

Labels (1)
1 Solution

Accepted Solutions
DavidM
Partner
Partner

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.

View solution in original post

4 Replies
DavidM
Partner
Partner

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
Creator
Creator
Author

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
Partner
Partner

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
Creator
Creator
Author

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