Numberpart from of a stringfield 2

[stillsteam]

Hello

CR 8.5 or 9
SQL

I need to get numberportion out of a stringfield.
Example data

ID String
1 36yfp/pall, pal 216, rle 1, yfp 6,
2 pal 30, rle 1,
3
4 rle 1,
5 pal 1,

I need to get the numbers after pal and before next comma.

Expected output
ID String
1 216
2 30
3
4
5 1

It's ok if ID 3 and 4 are 0(zero) also. I will use them as numbers in other formulas later on.
There can be any numbers of "strings" within that field between 0 and 10. They are always seperated by a comma if it's not null. As you can see in the sample above then "pal" can be anywhere in that string.
I've been searching alot in this forum but haven't foundanything that solve this completly.

Thank you in advance
Jonas

 

[CR85user]

Are the strings listed above always the same lengths? Or do they vary?

 

[Naith]

StringVar str := Mid({Your.Field},InStr(str,"pal ")+4);
Var(str)

Naith

 

[lbass]

Try:

if isnull({table.string}) then "" else
left(split({table.string},"pal ")[2],instr(split({table.string},"pal ")[2],",")-1)

Naith, the "var" function isn't available in 8.0 (and I'm not sure what it is).

-LB

 

[stillsteam]

Hello

Thanks for the replys.

The strings can vary in length

Naith
It didn´t work, CR didn´t recognize the Var function.

LB
I get a message that says "A subscript must be between 1 and the size of the array"
What could be the problem?

TIA
Jonas

 

[Naith]

lbass,

The Var function is available on 8.5, and is also supposed to be on 9. I guess it must be you on v8, as Jonas states he has both 8.5 and 9. Essentially, it's a string function which parses a string extracting numerics until it encounters a char, when it will then terminate. (e.g. Var('1234LISA') will return 1234.00).

However, I commented on another thread where I encountered a v9 installation where the var function was not recognised, whilst on another v9 install, it was. The difference between the two, was that the first install had all the hotfixes and service pack installed if applicable (CR Pro v9.2.3.1256). The second install was raw (CR Pro v9.2.3.970). This is obviously a bug with the software, and I'll probably raise it with BO. It sounds like my man Jonas is encountering the same problem.

I would be interested, Jonas, if you try the formula out on your 8.5 version, as I presume you tried it on your 9 and discovered the problem of var not being recognised. If you're encountering the problem on both, did you apply hotfixes to both installations?

Naith

 

[stillsteam]

Hello

Naith,

In 8.5 it says "The remaining text does not appear to be part of the formula."
The cursor stands just before the Var function.

I haven't tried it at all in V.9 yet because of I rather use 8.5, that is because I can´t distribute reports made in V.9

I haven´t applied any hotfixes on either versions.

Jonas

 

[Naith]

Jonas,

If you can't use Var, then remove the first line of lbass' formula, and replace it with

If InStr({Your.Field},'pal ') > 0 Then

and her solution should work for you. You were having issues with it before because of the data you have where there is data, but no 'pal ' entry.

Could you tell me what version of CR you're using? Is it Pro?

If anyone is reading this thread, are you also experiencing problems with the Var function?

Naith

 

[stillsteam]

Hello

Perfect!
I added Val()function to get it numeric.

Thank you all for your help.

Jonas

 

[GJParker]

Naith

I'm using CR10 developer and CR doesn't allow use of the Var() function in the manner you have used it. It is now used for Crustal OLAP only.

CR Help says

Var
Crystal OLAP Syntax.

Returns the unbiased variance (variance normalized to n-1) of a numeric expression evaluated over a set.

Syntax
Var(Set[,NumericExpression])

Parameters
Set
The set over which the variance is evaluated.

NumericExpression
An expression that evaluates to a numeric value.

Return value
Returns a numeric value.

Example
The following example returns the unbiased variance of the value of Sales for the members of the Products dimension.

Var( Members({Products}), {profit_loss@'Sales'} )

See also Members - Dimension.



Gary Parker
MIS Data Analyst
Manchester, England

 

[stillsteam]

Hello Naith

I use 8.5 Pro

/jonas

 

[stillsteam]

Hello

All of a sudden I got som problems with this formula.

Now it says
"String lenght is less than 0 or not an integer"
What could have happen?
This is the complete formula I use to extract the numberpart.

If InStr({ar.q_altenhet_generell},'pal ') > 0 Then
Val(left(split({ar.q_altenhet_generell},"pal ")[2],instr(split({ar.q_altenhet_generell},"pal ")[2],",")-1))

If I use this formula in another report it works fine.

regards
Jonas

 

[Naith]

Thanks Gary - that's what I see in the help files for one of the v9s I have too.

Jonas, take the Val function off, put Else '' at the end of the formula, and tell me what you get.

Can you confirm that the string "pal " will always be followed by a number and a comma even if it's at the end of a string?

Naith

 

[stillsteam]

Hello Naith

I got the same thing.

yes it will always be followed by numbers and end with a comma.

Jonas

 

[Naith]

If IsNull({ar.q_altenhet_generell},'pal ') 
Then ""
Else 
	If InStr({ar.q_altenhet_generell},'pal ') > 0 
	Then Val(left(split({ar.q_altenhet_generell},"pal ")[2],
	instr(split({ar.q_altenhet_generell},"pal ")[2],",")-1))
	Else ""

 

[stillsteam]

Hello Naith

Now I get the message "Too many arguments have been given to this function"

Jonas

 

[Naith]

Sorry, I didn't test. Remove the ",'pal '" out of the IsNull line.

 

[stillsteam]

Hello Naith
Now I get the message "A number is required here." and the cursor stands just before the last two "" in the code

If IsNull({ar.q_altenhet_generell}) 
Then ""
Else 
    If InStr({ar.q_altenhet_generell},'pal ') > 0 
    Then Val(left(split({ar.q_altenhet_generell},"pal ")[2],
    instr(split({ar.q_altenhet_generell},"pal ")[2],",")-1))
    Else [b]HERE[/b]""

Thanks for all your help.

Jonas

 

[Naith]

It's because you're using val. You can either remove that line altogether, or make it Else 0.

 

[stillsteam]

Hello Naith

Already tried that, then it says it require a string and stands before the second IF!?

Jonas