Formula Assistance - Invalid Data Type?

building reports webintelligence
1

Hi,

I have two queries. I want to make a formula to get some very specific information, but before I can do that, I have to have two similar objects from each query to start with, which I do not have. The closest objects are the same piece of data, but one contains some extra digits like below:

Object 1 = 123456-00001
Object 2 = 123456-001

My original formula says: If Object 1 = Object 2 than X. Obviously, nothing works because the data is not setup the same.

What I have tried is:

  1. Truncating the values. Even if I can get just the part before the dash, I can get what I need, but when I truncate it says “data type is not valid to truncate”

  2. Converting the data into a number, in hopes that then I could truncate, but I could not get that to work. Converting worked, but I want to chop off the last 3 digits… **here is my formula =ToNumber(Substr(Replace([Object 1];"#";"##");5;Length(Replace([Object 1];"#";"##"))))

Long story short, is there any way to achieve what I am looking for? Can anyone truncate the last 3 digits of the above? I would then try writing the formula to say: If Variable 1 = Variable 2, then X.

2

pay attention to the data type.
you cannot truncate a number and you cannot replace a single digit of a number, that only works in strings.
with numbers you have to calculate the result.
divide the string into seperate fields, transform this to the common target format and check if that logic is valid for ALL of your lines of data.
only when everything look good/equal, combine the content for merging your new object 1 and 2.

3

Rick,

Use a combination of Left and Pos functions to return the 123456 part:
=Left([FullString];Pos([FullString];"-"))

Then you have comparable strings to work with to get to where you want.

Your other option is to extend that to:
=Left([FullString];Pos([FullString];"-")+1)+Right([FullString];3)

1 Like