I have one numeric data source that is stated in a master synomyn (was not produced by me) at
Actual: D8 Usage: D20.2 format and another that is in Actual:P10 Usage:P20.4 format. I am trying to reconcile these two data sources to report the ones that didnt match through the Match command. What is the best way to coordinate these two data sources formats so they will successfully Match Proccess.

I have tried various formats but have issues where the program says that the two records dont match and they look exactly like each other. Especially the two fields I mention above.


Are there any issues that may contribute for this to behave like this? Any help?

Dan

This message has been edited. Last edited by: moyer1dl,

Because of the way D and F fields are stored internally they will never match a P even if
they both display the same.

1.2 is stored as 1.2 when it is a P
1.2 is stored as 1.1999999999999999 when it is a D

Convert both values to the same format preferably P and match on those values.

In my experience matching is best done using alpha fields as the matching fields. I agree with JG that you should convert the fields to the same format, but I'd make them alpha (using ftoa or ptoa where appropriate) by means of a define and then do the match on the alpha fields.

Dan,

If you run the following, you can get an idea what happens when you convert from D to P at different times in the flow of the fex.
I agree with JG that trying to match on D fields is hazardous. Matching on A or P fields works but you have to be sure when you convert from D.

  
DEFINE FILE CAR
D2RCOST/D9.2=RCOST/3.1416;
P2RCOST/P9.2=RCOST/3.1416;
END
TABLE FILE CAR
SUM RCOST D2RCOST P2RCOST COMPUTE DPRCOST/P9.2=D2RCOST;
BY COUNTRY
END

Thanks

Do you have an example of how to convert these over to alpha?

Sure, look up the user functions FTOA and PTOA.
If you also have devstudio installed somewhere, it comes with the help file called function.chm. This contains all descriptions of all user written functions.

Ok this is what I have constructed for the two data source fields I mentioned from above. They still dont seem to be matching.


COMPUTE OUTSTANDRESERVE2/A15 = FTOA(OUTSTANDRESERVE, '(D12.2)', OUTSTANDRESERVE2);


COMPUTE TRXBALANCE2/A14 = PTOA(TRXBALANCE, '(P12.2)', TRXBALANCE2);

Any Help

Dan,

As JG mentioned above you really have to undertsand the precision at which the data is stored. If you take two values of, say, 9.95 in either format then you could have 9.9499 in the D12.2 format and converting this to alpha is not going to match against the 9.95 from the P12.2 format.

You need to do some samples from your actual data and then determine the approach required. If I had the two values above I'd probably round the D12.2 value by multiplying by 100, taking the integer and then dividing by 100 placing the result in a P12.2. The match should work OK then.

Best approach? Look at your data first.

Oh, A14 and A15 formats might not match either

T

Be aware that the D format, ie Double precision floating point, contains separator characters - commas for those of us on the west side of the pond and periods for those on Tony's side.
The P format, ie packed decimal, does not.
This may affect the matching process.

quote:

periods for those on Tony's side

Commas in the UK Jim

T

With apologies to Shaw - separated by a comma language!

Mea culpa!

Ok when I use the FTOA in the Intial formating of the one data source I lose records in one to many relationships. For example I get one amount for an account when I should get two.

Here is my define statement

[CODE] OUTSTANDRESERVE2/A26 = FTOA OUTSTANDRESERVE, '(D20.2)', OUTSTANDRESERVE2);[CODE]

Do I need like a pre proccesor. I ran a little test to test the equality of values in the value would always say it was not equal but the other ones would be equal.

You should really convert both numbers to P22.4 and then match. If you have problems, print out detail data for the non-matches, with the D format with a higher precision (e.g. D18.6). You are on a very old release- on a later release you could have used D28.8 to display a more precise double value.

There is no need to use FTOA or PTOA: they simply convert to packed internally anyway to display the number.

Again, convert to P22.4 and investigate problem matches.

Edward or anybody,

Part of the problem I am having is the record seems to match on this field even though the amounts are different. It is as if it is not condsidering this field.

Here is the output after trying Edwards reccomendation. They look like they right justify, I dont know. (I used OLD-or-new to get somthing to print out)

OUTSTANDING
RESERVE          TRXBALANCE2
10410402.5400    10410401.9400
   31636.3000       31323.4000
 1472126.9300     1472056.0500
   51000.0000       51000.0000  

Here is the code

DEFINE FILE CHSHLD3
-*TRXBALANCE2/A26 = PTOA(TRXBALANCE, '(P12.2C)', TRXBALANCE2);
TRXBALANCE2/P22.4=TRXBALANCE;
END
DEFINE FILE LRPMHLD
-*OUTSTANDRESERVE2/A15 = FTOA(OUTSTANDRESERVE, '(D12.2)', OUTSTANDRESERVE2)
OUTSTANDRESERVE2/P22.4=OUTSTANDRESERVE;
END
MATCH FILE LRPMHLD

  BY CLAIMNUMBER2
  BY COVERAGE2
  BY OUTSTANDRESERVE2


RUN


FILE CHSHLD3
  PRINT
  CLMLOSSDATE
  CLMOPENDATE
  CLMCLOSEDATE
  CLMLASTACTIVITYDATE
  POLDISPLAYPOLICY
  BY CLAIMNUMBER2
  BY COVERAGE2
  BY TRXBALANCE2
AFTER MATCH HOLD NEW-NOT-OLD
END 
TABLE FILE HOLD
PRINT
     CLAIMNUMBER2 AS 'CLAIM #'
     COVERAGE2 AS 'COVERAGE'
	 OUTSTANDRESERVE2 AS 'OUTSTANDING,RESERVE'
     CLMLOSSDATE AS 'CLAIM,LOSS,DATE'
     CLMOPENDATE AS 'CLAIM,SETUP,DATE'
     CLMCLOSEDATE AS 'CLAIM,CLOSE,DATE'
     CLMLASTACTIVITYDATE AS 'CLM LAST,ACTIVITY,DATE'
     POLDISPLAYPOLICY AS 'POLICY #'
HEADING 

The biggest thing that is confusing me is the data sources are both SQL. The one synomyn for one of the Data Source generated the field into this P21.4. Is this normal?

I think at least when I view either of these data sources through MS Access they are using auto double.

This message has been edited. Last edited by: moyer1dl,

All SQL sources have specific rules about how the WebFocus adapter maps numerics and they are usually
related to the precision of the declared column in the DBMS.

If you are getting different formats between two tables in WebFocus it is because they are declared differently
in the tables.

You can find the rules for a particular adapter by going to the WebFocus server console and checking the online adapter
help.

If the have the same SQL format try changing the masters so that they have the same ACTUAL and USAGE, preferably P
and do your match using that.

You may need to get the DBA to adjust the tables so that they have the same precision.

Alternatively, keep the OLD-OR-NEW and then, beacuse you have to hold the data from a MATCH anyway, just output the rows where the difference is zero or within any bounds you might have to work to.

In one of your posts you mention a one to many relationship and only getting one value when you should have two. I therefore assume that you are performing a SUM operation? If so you will need to do any conversion to alpha for comparison after the summation and not before.

T

Dan,

In the first match, you only have 3 BY's. That means that under the covers a SUM is performed because that is the default verb. The second match has some PRINT fields, and also 3 BY's. My guess is that these three BY's should be the matching fields. If that is indeed the case, then all BY's must have the same format and the same name.
So, CLAIMNUMBER2 and COVERAGE2 and OUTSTANDRESERVE2 in the first MATCH should have exactly the same format as CLAIMNUMBER2 and COVERAGE2 and TRXBALANCE2 in the second MATCH.
But, the third field in both MATCHes must also have the same name for the MATCH to be performed correctly. You can do that by simply specifying
BY TRXBALANCE2 AS OUTSTANDRESERVE2
in your second MATCH.
Or of course
BY OUTSTANDRESERVE2 AS AMOUNT
in the first MATCH and
BY TRXBALANCE2 AS AMOUNT
in the second.
The hold file will then contain a field called AMOUNT.

Ok I think Gamp and Edwards solutions worked but now when I go to edit that amount field in the report I get and "uncorrectable syntax error". I want to push it down to two decimal places.

quote:

"uncorrectable syntax error"

One of the main causes is usually a missing ;

If you took Edwards solution to match on P22.4 format, then you would only need to say AMOUNT/P12.2.
If you converted to A, then in your final report you would need to convert it back to D or P again to be able to do calculations on it or redefine the display format.

Thanks All

I believe these issues have been resolved.