topic Re: How can I put if..else statement when load in QlikView

How can I put if..else statement when load

Sun, 19 Feb 2017 03:30:09 GMT

Major:

Load * INLINE [

Name

computer science engineering,

mechanical engineering,

mathematics,

applied mathematics,

politics,

international politics,

physics,

marketing

];

Major table has the value like above.

and I want to made new table that has Name and type field

if the Name contains string 'engineering', put value 'engineering' in type field.

if the Name contians string 'politics', put value 'politics' in type field

if the Name contains string 'marketing', put value 'maketing' in the field

if the Name doesn't have string among 'engineering','politics' or 'marketing', put value 'else' in the field.

Is there anybody know how to do this??

thank you in advance

Re: How can I put if..else statement when load

settu_periasamy — Sun, 19 Feb 2017 03:46:38 GMT

Try this..

Major:
LOAD *,if(SubStringCount(Name,'engineering'),'Engineering',
if(SubStringCount(Name,'politics'),'Politics',
if(SubStringCount(Name,'marketing'),'Marketing','Else'))) as Type;

Load * INLINE [
Name
computer science engineering,
mechanical engineering,
mathematics,
applied mathematics,
politics,
international politics,
physics,
marketing
];

Re: How can I put if..else statement when load

vinieme12 — Sun, 19 Feb 2017 06:47:55 GMT

Try like

Major:

LOAD *,

Pick(Wildmatch(Name,'*engineering*','*politics*','*marketing*')+1,'Else','Engineering','Politics','Marketing') as Type

;

Load * INLINE [

Name

computer science engineering,

mechanical engineering,

mathematics,

applied mathematics,

politics,

international politics,

physics,

marketing

];

Re: How can I put if..else statement when load

maxgro — Sun, 19 Feb 2017 21:31:49 GMT

Major:

Load

Name,

Pick(Wildmatch(Name,

'*engineering*', '*politics*', '*marketing*', '*' ),

'engineering', 'politics', 'marketing', 'else' ) as Type

INLINE [

Name

computer science engineering,

mechanical engineering,

mathematics,

applied mathematics,

politics,

international politics,

physics,

marketing

];

Re: How can I put if..else statement when load

MarcoWedel — Mon, 20 Feb 2017 23:44:16 GMT

Hi,

one solution using a table of possible Type values (that might be loaded from an externally maintained source) could be:

mapType:
Mapping
LOAD Type, '@start@'&Type&'@end@' INLINE [
    Type
    engineering
    politics
    marketing
];

Major:
LOAD Name, If(Len(Type),Type,'else') as Type;
LOAD *, TextBetween(MapSubString('mapType',Name),'@start@','@end@') as Type
Inline [
    Name
    computer science engineering
    mechanical engineering
    mathematics
    applied mathematics
    politics
    international politics
    physics
    marketing
];

hope this helps

regards

Marco

Re: How can I put if..else statement when load

rwunderlich — Tue, 21 Feb 2017 00:11:32 GMT

All of the proposed solutions are great and will work for this simple example. But when you have complex wildcard mapping requirements, don't forget your friendly helper, QVC http://qlikviewcomponents.org.

$(Must_Include=http://raw.githubusercontent.com/RobWunderlich/Qlikview-Components/master/Qvc_Runtime/Qvc.qvs);

WildMapTable:

LOAD * INLINE [

from, to

*engineering*, Engineering

*politics*, Politics

*marketing*, Marketing

*, Else

];

CALL Qvc.CreateWildMapExpression(vMapExpr, 'WildMapTable');

Major:

Load

Name,

$(vMapExpr(Name)) as Type

INLINE [

Name

computer science engineering,

mechanical engineering,

mathematics,

applied mathematics,

politics,

international politics,

physics,

marketing

];

DROP TABLE WildMapTable;

CALL Qvc.Cleanup

-Rob

http://masterssummit.com

http://qlikviewcookbook.com