topic Re: Searching for a Specific Phrase Within A String in QlikView

Searching for a Specific Phrase Within A String

kaygee28 — Tue, 22 Mar 2016 15:06:59 GMT

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.

Re: Searching for a Specific Phrase Within A String

Anonymous — Tue, 22 Mar 2016 15:30:52 GMT

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

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

Re: Searching for a Specific Phrase Within A String

Chanty4u — Tue, 22 Mar 2016 15:42:36 GMT

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

Re: Wildmatch Expression Error?

Re: Searching for a Specific Phrase Within A String

swuehl — Tue, 22 Mar 2016 16:30:08 GMT

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

Re: Searching for a Specific Phrase Within A String

kaygee28 — Tue, 22 Mar 2016 19:17:34 GMT

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

Re: Searching for a Specific Phrase Within A String

MarcoWedel — Wed, 23 Mar 2016 01:08:38 GMT

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

hope this helps

regards

Marco

Re: Searching for a Specific Phrase Within A String

MarcoWedel — Wed, 23 Mar 2016 01:14:49 GMT

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

Re: Searching for a Specific Phrase Within A String

kaygee28 — Wed, 23 Mar 2016 06:25:04 GMT

Thanks Marco!

Re: Searching for a Specific Phrase Within A String

MarcoWedel — Wed, 23 Mar 2016 06:54:27 GMT

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