How can I put if..else statement when load

[#1]

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

[settu_periasamy]

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
];

[vinieme12 · Accepted solution]

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

];

[maxgro]

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

];

[MarcoWedel]

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

[rwunderlich]

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