Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
My nested IFs are getting rather deep - is there a way I can replace them with a table? I want to keep everything in the script, no charts.
There is a kind of precedence implied in the order of the IFs.
Load
...
,If(WildMatch(OperatorName,'*bus *','*buses*','*minibus*') or WildMatch(OperatorName,'*coach*'),'Bus & Coach',
If(WildMatch(OperatorName,'*food*') or WildMatch(OperatorName,'*catering*'),'Food',
If(WildMatch(OperatorName,'*skip*') or WildMatch(OperatorName,'*waste*') or WildMatch(OperatorName,'*recycl*'),'Waste Management',
If(WildMatch(OperatorName,'*asphalt*') or WildMatch(OperatorName,'*surfacing*') or WildMatch(OperatorName,'*tarmac*'),'Tarmac',
If(WildMatch(OperatorName,'*rail*') or WildMatch(OperatorName,'*railway*'),'Rail Services',
If(WildMatch(OperatorName,'*demolition*') or WildMatch(OperatorName,'*dismantl*'),'Demolition',
If(WildMatch(OperatorName,'*scaff*') or WildMatch(OperatorName,'*scaff*'),'Scaffolding',
If(WildMatch(OperatorName,'*transport*') or WildMatch(OperatorName,'*haulage*') or WildMatch(OperatorName,'*logistic*'),'Transport & Logistics',
If(WildMatch(OperatorName,'*fuel*') or WildMatch(OperatorName,'*oil*') or WildMatch(OperatorName,'*lube*'),'Fuel / Oils',
If(WildMatch(OperatorName,'*tanker*') or WildMatch(OperatorName,'*bulk*'),'Tankers',
If(WildMatch(OperatorName,'*horse*') or WildMatch(OperatorName,'*equine*'),'Equine',
If(WildMatch(OperatorName,'*cement*') or WildMatch(OperatorName,'*concrete*'),'Concrete & Cement',
If(WildMatch(OperatorName,'*farm*') or WildMatch(OperatorName,'*dairy*'),'Farm & Dairy',
If(WildMatch(OperatorName,'*build*') or WildMatch(OperatorName,'*construc*'),'Construction & Building',
If(WildMatch(OperatorName,'*timber*') or WildMatch(OperatorName,'*forestry*'),'Timber & Forestry',
'Other'))))))))))))))) as [Prospect Type]
Best regards,
MJ
Hi Martyn,
Have you come across this Mapping using wildcard?
Might another option just be to have one very long wildmatch statement & then use the index returned to join/pick what you want (so WildMatch(OperatorName,'*bus *','*buses*','*minibus*','*coach*','*food*' … etc) & 1-4 return Bus & Coach & 5 returns Food?
Cheers,
Chris.
Did you tried this approach?
You can reduce the length by numbering like;
LOAD * INLINE [
OperatorName, Prospect, Operatornum
"bus,minibus,buses,coach", "Bus & Coach",1
"food,catering", Food, 2
];
and use
WildMatch(Operatornum=1,'Bus & Coach')
Hi,
another solution might be:
mapProspectType:
Mapping
LOAD SubField(OpNam,','),
'@start@'&ProspTyp&'@end@'
Inline [
OpNam, ProspTyp
"bus,buses,minibus,coach", Bus & Coach
"food,catering", Food
"skip,waste,recycl", Waste Management
"asphalt,surfacing,tarmac", Tarmac
"rail,railway", Rail Services
"demolition,dismantl", Demolition
"scaff,scaff", Scaffolding
"transport,haulage,logistic", Transport & Logistics
"fuel,oil,lube", Fuel / Oils
"tanker,bulk", Tankers
"horse,equine", Equine
"cement,concrete", Concrete & Cement
"farm,dairy", Farm & Dairy
"build,construc", Construction & Building
"timber,forestry", Timber & Forestry
];
tabSomeRandomOperatorNames:
LOAD OperatorName,
If(SubStringCount(mappedProspTyp,'@start@'),TextBetween(mappedProspTyp,'@start@','@end@',IterNo()),'Other') as [Prospect Type]
While IterNo() <= RangeMax(SubStringCount(mappedProspTyp,'@start@'),1);
LOAD *,
MapSubString('mapProspectType',OperatorName) as mappedProspTyp
Inline [
OperatorName
food operator
recycling operator
demolition operator
oiltankeroperator
farmhorseoperator
some unrelated operator
just another operator
];
hope this helps
regards
Marco