Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Replace nested IF

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

Tags (2)
4 Replies
chrismarlow
Valued Contributor

Re: Replace nested IF

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.

jebamalai
Valued Contributor

Re: Replace nested IF

jobsonkjoseph
Contributor III

Re: Replace nested IF

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

Hi,

another solution might be:

QlikCommunity_Thread_319055_Pic2.JPG

QlikCommunity_Thread_319055_Pic1.JPG

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