Help with IF please

[jcharles]

I have a table in which two of the columns are a number column and the other is a name column as follows:

BranchID	BranchName
1	Specialist - Tax
2	Durban
3	Northam
4	Specialist - Personal Finance
5	PMB

I want to say that wherever part of the word 'Spec' is found it must be aliased as a Specialist Department and if it does not have 'Spec' then it is a branch.

I tried this but when I do a list box on BRANCHTYPE it give me only BRANCH. How do I get this to work?

IF(BranchName = 'Spec*', 'SPECIALIST' , 'BRANCH') AS BRANCHTYPE

[MK_QSL]

IF(WildMatch(BranchName) = 'Spec*', 'SPECIALIST' , 'BRANCH') AS BRANCHTYPE

[MK_QSL · Accepted solution]

Or

IF(WildMatch(BranchName, 'Spec*'), 'SPECIALIST', 'BRANCH') as BRANCHTYPE

[sujeetsingh]

WildMatch () is the best optimised way to write it .

Well you can also write it as

IF(BranchName = 'Spec*' OR BranchName = 'SPECIALIST'

           OR BranchName = 'BRANCH') AS BRANCHTYPE

[MayilVahanan]

HI

Try like this

IF(WildMatch(BranchName, 'Spec*'), 'SPECIALIST', 'BRANCH') as BRANCHTYPE

WildMatch() helps to achieve this.

[jagan]

Hi,

Try like this in script

LOAD

*,

If(Findoneof('Specialist - Personal Finance', 'Spec'), 'SPECIALIST', 'BRANCH') as BRANCHTYPE

FROM DataSource;

Regards,

Jagan.

[Peter_Cammaert]

Jagan, I don't really understand what your code does except defining a BRANCHTYPE column that is filled with 'SPECIALIST' for every record loaded... Is there some magical trick in this function?

Peter

[jagan]

Hi Peter,

FindOneOf() - will return the count given substring in a string, if it is >0, then SPECIALIST otherwise BRANCH value is loaded into the BRANCHTYPE column.  Now you can use this column directly in Listbox/Dimension in chart.  Hope this helps you.

Regards,

Jagan.

[jonathandienst]

Manish Kachhia wrote:

Or

IF(WildMatch(BranchName, 'Spec*'), 'SPECIALIST', 'BRANCH') as BRANCHTYPE

This is the correct answer. The field will contain SPECIALIST if the branch name starts with Spec and BRANCH otherwise.

Jonathan

[Peter_Cammaert]

You're describing the index() function, aren't you?

Peter