4 Replies Latest reply: May 15, 2014 10:02 AM by joe.lovati _

# Corey's &quot;Column confusion&quot;

[URL="http://www.monarchforums.com/showthread.php?goto=newpost&t=2623"]Corey (garncor) originally posted[/URL] in the KnowledgeBase forum:

I am attempting to extract data from a report using Monarch 9 Pro. There are two aspects to this report that are giving me problems and I was hoping someone out there could give me a hand.

1.     When a value in one of the columns is negative, the minus sign appears after the value in question

2.     I don’t think the company that delivers this report to me ever foresaw any payment amounts above \$100,000

When a payment amount above \$100,000 appears on the report, the result is one column that sometimes contains a minus sign, which applies to the number to the left, and sometimes contains a digit, which is the first digit of the number to the right.

I’m trapping the different values to the left and right in different templates but when I make the “right-facing” trap big enough to include the minus sign (which applies to the column to the left of the one I’m working on), sometimes I end up with a number with minus signs on both sides, for example “-5431.51-” Unfortunately, Monarch evaluates a number sandwiched between two minus signs as “0.00.” Is there a way that I can have Monarch disregard this leading minus sign and only consider the characters following it?

Thanks,

Corey

/QUOTE

Reposted here to keep it in the regular discussion area for future reference.

• ###### Corey's &quot;Column confusion&quot;

So a typical example might look something like this?

5431.51 5431.51-5431.5115431.51 /codeWhere the first number is 5431.51, the second should be -5431.51, the third should be 5431.51, and the last is 15431.51?

If[/B] this never floats along the line (your decimals are always in the same positions), and /Bthe positions immediately to the left and right of the value can contain only a space, a minus sign, or a number 1 to 9, then here's my suggested solution.

Let's refer to those variable space/minus sign/number as unknown fields X, where there's a single character X field between each number, simply named X1 through X5, in these positions:

X       X       X       X       X[/code]Then to determine the actual value of each field, we need calculated fields each with a formula similar to this formula to derive the first value:

if(x2="-",

-1*if(x1 .notin. (" ","-"),

val(x1)*10000+Value1,Value1),

if(x1 .notin. (" ","-"),

val(x1)*10000+Value1,

Value1))[/code]

If the X value to the left of the value (X1) isn't "-" or " ", then add that relative value to the original value, and if the x value to the right of the the original value (X2) is "-" then negate the original value.

The slightly complicated formula should account for the (unlikely?) situation wherein you'd have a number in the left X position and a "-" in the right X position.

Duplicate the calculated field, changing the field references as necessary, and if my assumptions as described above are correct, then that ought to do it for you.

Clear as mud?

• ###### Corey's &quot;Column confusion&quot;

Hi,

I probably should have mentioned in my original post that I am not particularly technical.  But let me play around with this and see what happens.

Is there some way that I can upload a sample of the report to this forum (txt file or screenshot) for potential respondents to view?

Corey

• ###### Corey's &quot;Column confusion&quot;

I came across a similar issue and resolved it by first trapping the two fields and naming them with a PRE and then used an if statement to correct the data.  In my sample below, there is either a 1 or a - occuring in the same vertical column, the 1 in the millions position would be trapped in the wrong field.  But I extracted these two fields as characters and then used the following calc fields to correct the data.

Val(If(Substr(,1,1)=","," ",[PRE_COLLECTIONS DEBIT]))

Val(If(Substr(,1,1)=",",[PRE_COLLECTIONS DEBIT]+,[PRE_COLLECTIONS CREDIT]))

-- Collections --

Debit      Credit

-

1,065,608.50

213,294.94

137.36-

1,874.01

68,491.10

.33

/code