topic Re: Cannot output all data when using left & index in QlikView

Cannot output all data when using left & index

Fri, 22 Jul 2011 15:25:14 GMT

Hi,

I have a list of names of which some contain (

I am using the code below to take the values prior to the ( which works great.

left(a.name, index(a.name,'(') -1) as [Name]

However, the values without any ( aren't returned. What's the best way to add them in?

Thanks,

Gwyn

Mon, 25 Jul 2011 12:57:57 GMT

Hello Gwyn,

you should use an if()-statement similar to this:

Load . . .

If( index(a.name,'(')) > 0, left(a.name, index(a.name,'(') -1), a.name) AS Name,

. . .

Regards, Roland

perumal_41 — Mon, 25 Jul 2011 13:11:31 GMT

please upload ur file

nagaiank — Mon, 25 Jul 2011 13:23:59 GMT

Try the following script, it works for me.

SubField(a.Name,'(',1) as Name

Mon, 25 Jul 2011 13:47:46 GMT

Cheers Ronald, works great

Mon, 25 Jul 2011 13:48:31 GMT

Krishnamoorthy, also works great.

For some reason, I couldn't mark yours as correct as well. Maybe as I'd already marked Ronald's as correct.

Sorry!

erichshiino — Mon, 25 Jul 2011 14:10:30 GMT

You can try to use subfield instead of your expression:

=SubField(a.name, '(', 1)

It will return everything if it doesn't find the bracket

Regards,

Erich