Finding a .(period) in a calculation

[kallehval]

Hi guys

I have a field which for example look like this:

1.22

45.1

43.45

I need to extract what's in front of the "." to one calc field and whts behind to another calc field for sorting reasons.

Does anybody have an idea for this calculation?

Thanks in advance...

Kasper

Edited July 2, 2010 by Guest

[bcooney]

CalcLeft = Let (



n = Position ( Field ; "." ; 1 ; 1 ) ;



Left ( Field; n-1)

)

CalcRight = Let (



n = Position ( Field ; "." ; 1 ; 1 ) ;



Right ( Field ; Length ( Field) -n)

)

[Raybaudi]

Int( YourField ) returns the left part.

Filter ( Mod ( YourField ; 1 ) ; 1234567890 ) returns the right part.

N.B.I suppose that your decimal separator is a dot.

[kallehval]

Hi Bcooney

This did the trick - perfect! There is just one thing:

Some of the entries has no "." - just a number.

And others has a "*" instead of "."

These should also be listed together with the ones before the period: For example:

23.43

12

45.56

12.76

34*45

First field should look like:

23

12

45

12

34

So the calculation should look for a "." or "*" and list whats in front. And if there are no "." or "'*" the claculation should just list the whole number

All this is because of a poor planning of the database in the first place (7 years ago) - and now the database has 83000 entries!

Thanks again...

Edited July 2, 2010 by Guest

[TheTominator]

Hi guys

I have a field which for example look like this:

1.22

45.1

43.45

Does the example field actually have three lines of values or are you giving examples found in three separate records?

Edit: your followup post clarified the issue already

Edited July 2, 2010 by Guest

[TheTominator]

Hi Bcooney

Some of the entries has no "." - just a number.

And others has a "*" instead of "."

These should also be listed together with the ones before the period: For example:

23.43

12

45.56

12.76

34*45

First field should look like:

23

12

45

12

34

So the calculation should look for a "." or "*" and list whats in front. And if there are no "." or "'*" the claculation should just list the whole number

This should do the trick.

LeftWords( Substitute(theField; ["."; " "] ); 1)

for the left hand side of the field.

RightWords( Substitute(theField; ["."; " "] ); 1)

for the right hand side of the field.

If you also notice that you have entries that start with a "-" such as

-12.76

or

-34*45

you might want to use

If(GetAsNumber(theValue) < 0; "-"; "") &

LeftWords( Substitute(theValue; ["."; " "] ); 1)

when extracting the left hand side to preserve the "-".

Edited July 2, 2010 by Guest

[Raybaudi]

First field should look like:

23

12

45

12

34

How should look second field ?

Edit: what represented that dot ( or that asterisk ) in the old DB ? A multiplication symbol ?

[Raybaudi]

RightWords( Substitute(theField; ["."; " "] ); 1)

What if the value doesn't contain a dot, neither an asterisk ?

[TheTominator]

What if the value doesn't contain a dot, neither an asterisk ?

Good catch. Thank you.

That formula is not sufficient.

Edited July 2, 2010 by Guest
Needed more coffee.

[TheTominator]

This should do it.

For a field named "theValue" ...

Let(

[

theWords = Substitute(theValue; ["."; " "]);

digits = "0123456789"

];

/* Left hand side */

Filter( MiddleWords("a a" & theWords & "a a"; 2; 1); digits)

& " --- " &

/* Right hand side */

Filter( MiddleWords("a a" & theWords & "a a"; 3; 1); digits)

)

Use either the Left hand side or the right hand side expressions as needed. Note that negative numbers are not dealt with in a specified way.

[Raybaudi]

What about:

Left part:

Let(

n = Substitute ( YourField ; [ "." ; ¶ ] ; [ "*" ; ¶ ] ) ;

GetValue ( n ; 1 )

)

Right part:

Let(

n = Substitute ( YourField ; [ "." ; ¶ ] ; [ "*" ; ¶ ] ) ;

GetValue ( n ; 2 )

)

[TheTominator]

What about:

Left part:

Let(

n = Substitute ( YourField ; [ "." ; ¶ ] ; [ "*" ; ¶ ] ) ;

GetValue ( n ; 1 )

)

Right part:

Let(

n = Substitute ( YourField ; [ "." ; ¶ ] ; [ "*" ; ¶ ] ) ;

GetValue ( n ; 2 )

)

I prefer your method for its elegance, but I worry that the data set in this case isn't as simple as stated so far. From what it sounds like, humans have been involved in these 83000 entries.

If an "*" came up on a second look, what other characters will come up as a separator when the data is examined further?

[kallehval]

Hi Tominator

You are absolutely right, humans have been involved ;-) and the data is not very cinsistant.

This is whats working for me so far:

My left field:

LeftWords( Substitute(TheField; ["."; " "] ); 1)

Right field (temporarycalculation)

Let (n = Position ( TheFIeld ; "." ; 1 ; 1 ) ;

Right ( Kap ; Length ( Kap) -n)

)

Right field

LeftWords( Substitute(calc_temp; ["*"; " "] ); 1)

It is dirty, but I actually end up with the correct data. My only problem is, that the right field should come up empty if there is no "." in "TheField".

Now it shows up as:

TheField: 20

Left: 20

Right: 20