Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
You can use index. Something like:
if(index(YourField,'-')=0,YourField) as WhatEver,
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.
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,
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.
How about:
if(len(keepchar(Mid([BRN-ID], 3, 7),'0123456789')=7,Mid([BRN-ID], 3, 7)) as [LOB Template Order],
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.
Sounds right to me.
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],
I've marked this as the correct answer because it does work. Thank you very much for your help.