Searching for a Specific Phrase Within A String

[kaygee28]

Good Day

I am currently stuck and I need help once again, I have strings of data that contain vehicle names for instance I have a BMW 320d F30 model, now what I want to do is to group all the various 3 series models into one for instance there is a BMW 320d F30, BMW 335i model etc etc. and I want these to be named BMW 3 Series, similar with all the vehicles within the BMW group.

Now the current problem I am faced with is that I have a table which I used a vlookup onto hence the field within that table has the vehicle model names i.e the Applymap function, which seems to be interfering with this if function I am trying to insert.

please see my code below:

Vehicle_Map:

Mapping LOAD DealRef,

     AssetDesc

FROM

[Demographica Portfolio Info V3 Data Models.xlsx]

(ooxml, embedded labels, table is [Demographica Portfolio Info V3 ]);

Demographics:

LOAD APPStatus,

     Contract_Ref_no,

     Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

     Start_Day,

     Start_Month,

     [Start Year],

     Status_cd,

     title_cd,

     Customer_name,

     race_type_cd,

     Gender

If((EDUCATION_EXPENSE_AMT)>0,'Parent','Not A Parent') AS [Parents/NotParents]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

now the if function I want to insert is:

if(wildmatch(AssetDesc,'BMW 3*', '3 Series Model') AS [Cars Bought]

however I keep getting errors is there a way in which I can search for this string using a different methodology.

Thanks in advance.

[Anonymous]

Looks like you have more opening that closing brackets, try something like this.

if(wildmatch(AssetDesc,'BMW 3*'), '3 Series Model') AS [Cars Bought]

[Chanty4u]

If(WildMatch([AssetDesc],'*BMW 3*'), '3 Series Model') as [Cars Bought]

• Re: Wildmatch Expression Error?

[swuehl · Accepted solution]

You are creating this field [AssetDesc] in your LOAD, so you need to use a preceding LOAD to access the new field (within a LOAD statement, only input table field names be referenced directly):

Demographics:

LOAD *,

if(wildmatch(AssetDesc,'BMW 3*'), '3 Series Model') AS [Cars Bought];

LOAD APPStatus,

     Contract_Ref_no,

     Applymap('Vehicle_Map',Contract_Ref_no,'NULL') AS AssetDesc,

     Start_Day,

     Start_Month,

     [Start Year],

     Status_cd,

     title_cd,

     Customer_name,

     race_type_cd,

     Gender

If((EDUCATION_EXPENSE_AMT)>0,'Parent','Not A Parent') AS [Parents/NotParents]

FROM

[BMW Consolidated SF Data 10032016.xlsx]

(ooxml, embedded labels, table is [BMW (45)]);

[kaygee28]

swuel it worked thanks a lot for your help including everyone else's input, yes I had more opening brackets than closing hence it wouldn't have worked anyway, but I had to have a preceding load before the demographics table, something new I learnt.

I really appreciate it!!!

[MarcoWedel]

Hi,

maybe one solution could be also:

LET vSubStrDel = Hash128(Rand());  //substring delimiter unlikely to occur in source data

mapSeries:
Mapping
LOAD ModelSubstring,
    '$(vSubStrDel)'&Series&'$(vSubStrDel)'
Inline [
ModelSubstring, Series
BMW 1,1 Series Model
BMW 2,2 Series Model
BMW 3,3 Series Model
BMW 4,4 Series Model
BMW 5,5 Series Model
BMW 6,6 Series Model
BMW 7,7 Series Model
];

table1:
LOAD *,
    TextBetween(MapSubString('mapSeries',Model),'$(vSubStrDel)','$(vSubStrDel)') as Series
Inline [
Model
BMW 116d
BMW 118i
BMW 216d
BMW 225i
BMW 320d
BMW 340i
BMW 420i
BMW 430d
BMW 535d
BMW 535i
BMW 550i
BMW 640d
BMW 650i
BMW 750i
];

see also: Re: Creating Field Based on String of Another Field

hope this helps

regards

Marco

[MarcoWedel]

you could as well use an expression like

Left(SubField(Model,'BMW ',2),1)&' Series Model'

in either script or front end.

hope this helps

regards

Marco

[kaygee28]

Thanks Marco!

[MarcoWedel]

Please close your thread by selecting a correct answer (and helpfuls if you like) or ask related questions if your issue is not solved.

Thanks

Regards

Marco