topic Re: Need To Suppress Values That Contain A - (dash) in QlikView

Need To Suppress Values That Contain A - (dash)

pnn44794 — Sun, 13 Oct 2019 00:22:22 GMT

Hello:

I have a need to not display values in a particular column that contain a dash anywhere within the field value. Blanks are okay and valid. The column in question is column E, LOB Template Order, in the attached sample data. I would like to do this from within the Load Script. Currently to get the LOB Template Order value, I have Mid(BRN, 3, 7) as [LOB Template Order] in the Load Script. As you can see, LOB Template Order is derived from the BRN value. Is there a way to enhance the Mid statement to do what I want or do I need a different line to do this with?

As always, thanks in advance for any and all help.

Re: Need To Suppress Values That Contain A - (dash)

m_woolf — Wed, 22 Nov 2017 19:56:10 GMT

You can use index. Something like:

if(index(YourField,'-')=0,YourField) as WhatEver,

Re: Need To Suppress Values That Contain A - (dash)

pnn44794 — Wed, 22 Nov 2017 19:59:44 GMT

Hello m w:

Thanks for the quick reply. Can I use this with the Mid statement above? Otherwise, I think I would have to use this as a calculated dimension which could cause the straight table to load slowly.

Re: Need To Suppress Values That Contain A - (dash)

m_woolf — Wed, 22 Nov 2017 20:22:40 GMT

I don't know exactly what you want. You can combine the script anyway you like:

if(index(YourField,'-')=0,YourField,Mid(BRN, 3, 7) ) as WhatEver,

Re: Need To Suppress Values That Contain A - (dash)

pnn44794 — Wed, 22 Nov 2017 20:39:21 GMT

Sorry m w. I should have been more clear. To get the LOB Template Order value, I use the Mid function on the BRN field and I simply start at the third position and take the next seven characters. The problem with that is I wind up getting these additional values that have a dash in them when I only want 7 digits and if it isn't seven digits, skip it.

I hope that makes more sense. I did use your original reply as a calculated dimension and it does indeed work. If you have no other suggestions based on what I just tried to explain, I'll mark your first answer as correct.

Thank you very much for your help.

Re: Need To Suppress Values That Contain A - (dash)

m_woolf — Wed, 22 Nov 2017 20:54:52 GMT

How about:

if(len(keepchar(Mid([BRN-ID], 3, 7),'0123456789')=7,Mid([BRN-ID], 3, 7)) as [LOB Template Order],

Re: Need To Suppress Values That Contain A - (dash)

pnn44794 — Wed, 22 Nov 2017 21:02:05 GMT

One question. In my Load script and you would not have known this, I actually load BRN as BRN-ID to avoid any linkage to another table. So, should I change BRN-ID to just BRN? I think I should, but you would know best.

Re: Need To Suppress Values That Contain A - (dash)

m_woolf — Wed, 22 Nov 2017 21:04:48 GMT

Sounds right to me.

Re: Need To Suppress Values That Contain A - (dash)

pnn44794 — Wed, 22 Nov 2017 21:20:50 GMT

I get the following error when running the Load script:

Error in expression: LEN takes 1 parameter

I copied and pasted this in:

if(len(keepchar(Mid([BRN], 3, 7),'0123456789')=7,Mid([BRN], 3, 7)) as [LOB Template Order2],

Re: Need To Suppress Values That Contain A - (dash)

pnn44794 — Wed, 22 Nov 2017 21:51:01 GMT

I've marked this as the correct answer because it does work. Thank you very much for your help.