topic Re: Help with IF please in QlikView

Help with IF please

jcharles — Thu, 13 Feb 2014 06:55:19 GMT

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

Re: Help with IF please

MK_QSL — Thu, 13 Feb 2014 06:56:50 GMT

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

Re: Help with IF please

MK_QSL — Thu, 13 Feb 2014 06:58:31 GMT

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

Re: Help with IF please

sujeetsingh — Thu, 13 Feb 2014 06:59:47 GMT

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

Re: Help with IF please

MayilVahanan — Thu, 13 Feb 2014 07:03:28 GMT

Try like this

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

WildMatch() helps to achieve this.

Re: Help with IF please

jagan — Thu, 13 Feb 2014 07:04:37 GMT

Hi,

Try like this in script

LOAD

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

FROM DataSource;

Regards,

Jagan.

Re: Help with IF please

Peter_Cammaert — Thu, 13 Feb 2014 07:22:43 GMT

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

Re: Help with IF please

jagan — Thu, 13 Feb 2014 07:45:10 GMT

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.

Re: Help with IF please

jonathandienst — Thu, 13 Feb 2014 08:05:17 GMT

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

Re: Help with IF please

Peter_Cammaert — Thu, 13 Feb 2014 08:05:21 GMT

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

Peter

Re: Help with IF please

jagan — Thu, 13 Feb 2014 08:35:41 GMT

Yes, this is similar to Index().

Regards,

Jagan.

Re: Help with IF please

jcharles — Thu, 13 Feb 2014 09:47:04 GMT

Thank you - This worked

Re: Help with IF please

MK_QSL — Thu, 13 Feb 2014 09:52:09 GMT

Glad that my answer help you.

Can you close this thread by selecting correct answer?