Using a Wildmatch to link dimesion to fact table

[#1]

So I have a fact table with a field called 'Policy' where the field is a nicely formatted string as seen below and I was given an excel document that needs to be added to the data set as a dimension. The only way to link the excel dimensional table to the fact table is by the 'Policy' but my issue is the Policy name is embedded in a string that is manually inputted by a user to the format of the sting is not consistent every time so I cannot simpily parse what I need out of the string and there are something around 300+ policies that I am working with so writing a huge if is out of the question.

I was trying to do a wildmatch between table 1 and table 2 to search for the value of 'Policy' field in the fact table in the values of 'Policy2' in the dim table but I cannot get that to work.

Has anyone had to do something similar to this in the past? Any help would be awesome. I have been driving myself crazy with this today.

Chris

Table #1 - Fact Table
Policy	Event ID
PU_AL_SEP__FOUND	667
PU_AL_SEP__NOT_FOUND	656
…	567
…	765
…

Table #2 - Dim Table
Policy2	Status	etc
123 - sdfsdfsdfsdfsd (PU_AL_SEP_FOUND)	1	r
1234- adasd PU_AL_SEP__NOT_FOUND	2	d
…	3	s
…
…

[vinieme12]

Use below

TableA:

LOad * inline [

Policy,EventID

PU_AL_SEP__FOUND,667

PU_AL_SEP__NOT_FOUND,656

];

mapA:

Mapping

LOAD Policy,

    '@start'&Policy&'end@'

Resident TableA;

OUTPUT:

LOAD *, TextBetween(MapSubString('mapA',Policy2),'@start','end@')  as Policy

inline [

Policy2,Status,etc

23 - sdfsdfsdfsdfsd (PU_AL_SEP__FOUND),1,Y

1234- adasd PU_AL_SEP__NOT_FOUND,2,X

];

[MarcoWedel]

Hi,

maybe helpful:

Re: Grouping List Box values

Re: Load script find value from one table as substring

Re: Create Filter using words of certain Field

regards

Marco