Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
kaygee28
Contributor III
Contributor III

Searching for a Specific Phrase Within A String

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)]);

View solution in original post

8 Replies
Anonymous
Not applicable

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

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

swuehl
MVP
MVP

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
Contributor III
Contributor III
Author

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:

QlikCommunity_Thread_210477_Pic1.JPG

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.

QlikCommunity_Thread_210477_Pic2.JPG

hope this helps

regards

Marco

kaygee28
Contributor III
Contributor III
Author

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