I am somewhat new to all this but have created a *match* which almost yeilds the results I desire. In both tables I have a fields called TRX_PAY_CODE. The trouble is I want BOTH the TRX_PAY_CODE values in my final table; the value found in table1 as well as table2. It gives me only the value from the first table.
In fact, syntatically I do not see how it could differentiate one from the other, not do I know how to tell it to do so. Assisitance is appreciated...THANKS.

MATCH FILE HTEMP
PRINT
HTEMP.HTEMP.GROUP_NUMBER
HTEMP.HTEMP.PATIENT_ID
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.TRX_PAY_CODE
HTEMP.HTEMP.TRX_FSC_Number
HTEMP.HTEMP.CurrentFSC
BY HTEMP.HTEMP.Invoice_Number
RUN
FILE HTEMP2
PRINT
HTEMP2.HTEMP2.TRX_PAYMENT_AMOUNT
HTEMP2.HTEMP2.TRX_PAY_CODE
BY HTEMP2.HTEMP2.Invoice_Number
AFTER MATCH HOLD AS HTEMP3 OLD-AND-NEW
END
TABLE FILE HTEMP3
PRINT
HTEMP3.HTEMP3.GROUP_NUMBER
HTEMP3.HTEMP3.PATIENT_ID
HTEMP3.HTEMP3.PAT_MRN
HTEMP3.HTEMP3.PAT_NM NOPRINT
HTEMP3.HTEMP3.Invoice_Number
HTEMP3.HTEMP3.Date_Of_Service
HTEMP3.HTEMP3.TRX_PostingDt
HTEMP3.HTEMP3.TRX_FSC_Number
HTEMP3.HTEMP3.CurrentFSC
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Try to set ASNAMES.

At the top of the FEX file initiate SET ASNAMES = ON and in the actual code

Example:

HTEMP.HTEMP.TRX_PAY_CODE AS PAY_CODE_FROM_TABLE1

HTEMP2.HTEMP2.TRX_PAY_CODE AS PAY_CODE_FROM_TABLE2

NOW IN HTEMP3 you can call those 2 alias names

THANKS! I had tried using the AS verb but it did not run...I have since added the SET command and then amended my table1 amnd table2 routines and now it takes that syntax and allows me to output each individually after the match!!!

HOWEVER..I now have another problem that is somehow a byproduct of all that.

Table1 has 44 records. Table2 had 1.9 million. After the match I should get at most 44 records...in fact I was getting just that until I issues the SET AS NAMES and brought in both fields.

Now I get 1.9M records!!!!

MATCH FILE HTEMP
PRINT HTEMP.HTEMP.GROUP_NUMBER HTEMP.HTEMP.PATIENT_ID HTEMP.HTEMP.PAT_MRN HTEMP.HTEMP.PAT_NM HTEMP.HTEMP.Date_Of_Service HTEMP.HTEMP.TRX_PostingDt HTEMP.HTEMP.TRX_FSC_Number HTEMP.HTEMP.CurrentFSC HTEMP.HTEMP.TXPAYCD1
BY HTEMP.HTEMP.Invoice_Number
RUN
FILE HTEMP2
PRINT HTEMP2.HTEMP2.TRX_PAYMENT_AMOUNT HTEMP2.HTEMP2.TXPAYCD2
BY HTEMP2.HTEMP2.INV
AFTER MATCH HOLD AS HTEMP3 OLD-AND-NEW
END
TABLE FILE HTEMP3
PRINT
HTEMP3.HTEMP3.INV
HTEMP3.HTEMP3.TRX_PAYMENT_AMOUNT
HTEMP3.HTEMP3.TXPAYCD1
HTEMP3.HTEMP3.TXPAYCD2
HTEMP3.HTEMP3.GROUP_NUMBER
HTEMP3.HTEMP3.PATIENT_ID
HTEMP3.HTEMP3.PAT_MRN
HTEMP3.HTEMP3.PAT_NM NOPRINT
HTEMP3.HTEMP3.Invoice_Number
HTEMP3.HTEMP3.Date_Of_Service
HTEMP3.HTEMP3.TRX_PostingDt
HTEMP3.HTEMP3.TRX_FSC_Number
HTEMP3.HTEMP3.CurrentFSC
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


Only difference I can see is the references to: TXPAYCD1
and TXPAYCD2

I thin kthat you need to check your code and make sure that you are matching on the same Field. In your lastest post the "BY" field names from HTEMP and HTEMP2 do not match.

I suspect your data can have multiple payments per invoice.

You should not use PRINT / PRINT in MATCH FILE, if one-to-many matches are possible and you want to capture them all.

quote:

I thin kthat you need to check your code and make sure that you are matching on the same Field. In your lastest post the "BY" field names from HTEMP and HTEMP2 do not match.

Ok, I did not realize the BY files had to have the EXACT same name. Is this true. What if the key values have differing names in the two tables...must you see to it that they are the same. Anyway, I made them the same and I think its now working...I must validate. Thanks!

further....I have another situation where the BY fields in the two tables to be matched have different names: t1.Invoice_Number (upper and lower case) and t2.INV.

The match won't work....different names I went back to the t2 routine and altered the code to store t2.INV as t2.Invoice_Number. Now it should Match!

I redid the match BUT....it appears Webfocus has altered my t2.Invoice_Number to: t2.INVOICE_NUMBER....it appears as mixed case in the hold file but upper case in the match GUI! So, it still does not work...I assume its because the BY fields are of differing names; one mixed case, one all upper case.

and finally...the fields are of differing types...t1.Invoice_Number is AV8 and t2.Invoice_Number is I11.

So my thought was to go back to my original table and add the field: t1.Invoice_Number as: t1.INVOICE_NUMBER using the *AS* variable and also to convert it from Av8 to I11 from the get go.

Does this sound reasonable? What is the code to convert from AV8 to I11 on the fly...

This is a learning experience for sure!

The BY fields for match statements need to be the same name and the same format.


You could use a DEFINE FILE statement.

SET ASNAMES = ON

DEFINE FILE T1
INVOICE_NUMBER_I11/I11=EDIT(Invoice_Number);
END

MATCH FILE T1
...
BY INVOICE_NUMBER_I11 AS 'INVOICE_NUMBER'
...

THANKS! I am getting there...allbeit slowly!

Hi,
Just a small correction. The names of the BY fields don't need to be the same.
You could use the AS syntax for the BY field in the MATCH to "match" them together.
E.g.
MATCH FILE CAR
SUM SALES
BY COUNTRY AS 'MYCOUNTRY'
RUN
FILE OTHERCARS
SUM CONTINENT
BY MYCOUNTRY
END

would also work.

thanks again...I thought I tried that but it did not work...I will try again-it would be easier that way.