Need To Suppress Values That Contain A - (dash)

[pnn44794]

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.

[m_woolf]

You can use index. Something like:

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

[pnn44794]

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.

[m_woolf · Accepted solution]

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,

[pnn44794]

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.

[m_woolf]

How about:

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

[pnn44794]

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.

[m_woolf]

Sounds right to me.

[pnn44794]

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

[pnn44794]

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