topic Re: Replace nested IF in QlikView

Replace nested IF

martynlloyd — Tue, 06 Nov 2018 13:59:48 GMT

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,

Re: Replace nested IF

chrismarlow — Tue, 06 Nov 2018 14:15:02 GMT

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.

Re: Replace nested IF

Anonymous — Tue, 06 Nov 2018 14:29:18 GMT

Did you tried this approach?

Replace Multiple If Statements | Qlik Community

Re: Replace nested IF

jobsonkjoseph — Tue, 06 Nov 2018 14:36:01 GMT

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

Re: Replace nested IF

MarcoWedel — Tue, 06 Nov 2018 21:38:48 GMT

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