Replace nested IF

[martynlloyd]

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

[chrismarlow]

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.

[Anonymous]

Did you tried this approach?

Replace Multiple If Statements | Qlik Community

[jobsonkjoseph]

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')

[MarcoWedel]

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