Table HTEMP has 44 rows. The BY field combinations:HTEMP.HTEMP.PATIENT_ID and
HTEMP.HTEMP.TRXFSCNBRKEY repeat for several records in HTEMP. Ex: A patient (PATIENT_ID) has a particular insurance (TRXFSCNBRKEY )and that patient happens to have more than one invoice in HTEMP.

Table HTEMPPATFSCINFO has tens of thousands of records. Each combination of PATIENT_ID and TRXFSCNBRKEY is UNIQUE in this table. That key yeilds a copay amount.


Essentially what I am trying to do is pull the Copay amount for each of the 44 records in HTEMP.

My resultant dataset does indeed have 44 records. Yea! The trouble is, on the records that have multiple 'BY' field combinations, the copay amount only shows on ONE record.

For example, Joe Smith has 5 records (invoices) with PATIENT_ID = 111 and TRXFSCNBRKEY =112. The combination of the PATIENT_ID and TRXFSCNBRKEY yeild ONE record in: HTEMPPATFSCINFO and that record indicates a COPAY of $10. All 5 records are indeed in the resultant data set but only ONE shows the copay = 10....the rest show a blank copay. This happens only when the BY field combination occurs multiple times in HTEMP. Why?

MATCH FILE HTEMP
PRINT
HTEMP.HTEMP.Group_Number
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Invoice_Number
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.CurrentFSC
BY HTEMP.HTEMP.PATIENT_ID
BY HTEMP.HTEMP.TRXFSCNBRKEY
RUN
FILE HTEMPPATFSCINFO
PRINT HTEMPPATFSCINFO.HTEMPPAT.COPAY_AMOUNT
BY HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID
BY HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY
AFTER MATCH HOLD AS HTEMP6 OLD-AND-NEW
END
TABLE FILE HTEMP6
PRINT
HTEMP6.HTEMP6.PATIENT_ID
HTEMP6.HTEMP6.Group_Number
HTEMP6.HTEMP6.PAT_MRN
HTEMP6.HTEMP6.PAT_NM
HTEMP6.HTEMP6.Invoice_Number
HTEMP6.HTEMP6.Date_Of_Service
HTEMP6.HTEMP6.TRX_PostingDt
HTEMP6.HTEMP6.CurrentFSC
HTEMP6.HTEMP6.TRXFSCNBRKEY
COMPUTE COPAYNULLFLAG/A20 = IF HTEMP6.HTEMP6.COPAY_AMOUNT EQ '' THEN 'Y' ELSE 'N';
HTEMP6.HTEMP6.COPAY_AMOUNT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HTEMP7 FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
TABLE FILE HTEMP7
SUM
HTEMP7.HTEMP7.COPAY_AMOUNT
BY LOWEST HTEMP7.HTEMP7.PATIENT_ID
BY LOWEST HTEMP7.HTEMP7.Group_Number
BY LOWEST HTEMP7.HTEMP7.PAT_MRN
BY LOWEST HTEMP7.HTEMP7.PAT_NM
BY LOWEST HTEMP7.HTEMP7.Invoice_Number
BY LOWEST HTEMP7.HTEMP7.Date_Of_Service
BY LOWEST HTEMP7.HTEMP7.TRX_PostingDt
BY LOWEST HTEMP7.HTEMP7.CurrentFSC
BY LOWEST HTEMP7.HTEMP7.TRXFSCNBRKEY
BY LOWEST HTEMP7.HTEMP7.COPAYNULLFLAG
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

You'll need to SUM instead of PRINT the COPAY_AMOUNT in the MATCH. The difference is covered in the "Fine-Tuning MATCH Processing" page in the documentation.

If file 1 is 44 records and file 2 is a gazillion records, MATCH FILE is not a good idea -- it will form a an internal matrix of 44 rows, and a second internal matrix of a gazillion rows, and then discard almost all of the latter as it merges the two streams.

You will be better off setting up a JOIN (HTEMP to HTEMPPATFSCINFO, left-outer, unique, on the two key columns), and doing a simple TABLE on HTEMP. -- Assuming HTEMP is a local Hold file, and HTEMPPATFSCINFO is a database table, WF will set up a lookup proc and execute it 44 times; that's ok, it beats the MATCH FILE process hands down.

If you want to use MATCH FILE in a case like yours, you should generate a third key field (a sequencer) in HTEMP, and then do a 3-key/2-key merge:

TABLE FILE HTEMP
LIST
 HTEMP.HTEMP.Group_Number
 HTEMP.HTEMP.PAT_MRN
 HTEMP.HTEMP.PAT_NM
 HTEMP.HTEMP.Invoice_Number
 HTEMP.HTEMP.Date_Of_Service
 HTEMP.HTEMP.TRX_PostingDt
 HTEMP.HTEMP.CurrentFSC
 BY HTEMP.HTEMP.PATIENT_ID
 BY HTEMP.HTEMP.TRXFSCNBRKEY
ON TABLE HOLD AS HTEMPB
END
?FF HTEMPB
MATCH FILE HTEMPB
SUM
 FST.Group_Number
 FST.PAT_MRN
 FST.PAT_NM
 FST.Invoice_Number
 FST.Date_Of_Service
 FST.TRX_PostingDt
 FST.CurrentFSC
 BY FST.PATIENT_ID
 BY FST.TRXFSCNBRKEY
 BY LIST
RUN
FILE HTEMPPATFSCINFO
SUM
    HTEMPPATFSCINFO.HTEMPPAT.COPAY_AMOUNT
 BY HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID
 BY HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY
AFTER MATCH HOLD AS HTEMP6 OLD-AND-NEW
END

This message has been edited. Last edited by: j.gross,

Thanks guys...still trying to absorb all this. Both files are local hold files. I immediately noticed joins don't seem to play well with hold files and learned of the match here in this forum. I will look into the SUM solution. Thanks. Is the join solution still a possibility with two hold files?

The SUM did not seem to do anything....was this what you had in mind?


MATCH FILE HTEMP
PRINT
HTEMP.HTEMP.Group_Number
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Invoice_Number
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.CurrentFSC
BY HTEMP.HTEMP.PATIENT_ID
BY HTEMP.HTEMP.TRXFSCNBRKEY
RUN
FILE HTEMPPATFSCINFO
SUM HTEMPPATFSCINFO.HTEMPPAT.COPAY_AMOUNT
BY HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID
BY HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY
AFTER MATCH HOLD AS HTEMP6 OLD-AND-NEW
END

No: as you said, changing the second PRINT to SUM accomplishes nothing -- basically because there is only one such record per set of key values.

In general, whenever you use
print x by a by b

a tiebreaker column ("N") is added to the internal matrix:
a b N x

(If you HOLD FORMAT FOCUS, that column is retained as foclist or foc$list)

and the MATCH acts as if N were a database column and you had coded
PRINT x BY a BY b BY N.

That explains why copay was zero for the excess records: N = 1,2,3,4 (respectively) for the series of (say) four invoices of a particular patient and contract (or whatever that second key stands for), and N=1 for the sole "matching" record in the second file (HTEMPPATFSCINFO) -- so the three keys of the corresponding row in the second internal matrix only match the three keys of the *first* of the series of 4 rows in the first internal matrix; the remaining rows are unmatched, hence their copay value remains zero or null.


The only way to avoid interference from that tiebreaker column is to eliminate it, by using SUM in the first leg of the match. But that would collapse rows in the first file that have identical keys. -- So either you need to create your own tiebreaker column (as I did with LIST) and use SUM with three sort keys in the first leg -- or include existing attribute columns that can break the ties -- or abandon MATCH FILE in favor of JOIN + TABLE -- as I outlined earlier.

RobertF,

Based on your description what you need to do is after the Match, sort the hold file by the 2 ket fields and then the copay in descending order and copy the copay from record to record when the 2 key fields match. The best way to do this is using a define that checks the current keys to the LAST keys.

thanks, again I need to time to absorb this *new* way of thinking....how a simple join can be made so complicated...

Robert,

HOLD files work just fine with JOIN. But you have to make sure that the files you are joining are sorted the same on the join key, or (as I recently learned) use HOLD FORMAT FOCUS INDEX fieldname.

I used to do a lot of MATCH processing, but now it's almost all joins..

I wish I could be more successful with joins.

I just took my HTEMP file. I buit a new hold file: HOLDSORTED with fields PATIENT_ID, TRXFSCNBRKEY and Invoice_Number. The file is sorted by PATIENT_ID and TRXFSCNBRKEY.

I next joined it to hold file:HTEMPPATFSCINFO, also sorted by PATIENT_ID and TRXFSCNBRKEY and those columns are the same type as in HOLDSORTED.

It did not work....now I get no copaymnet amt ever! Code is below...

Use a join? Use a match? Heck, maybe just cheat and run a sql passthru where things work normally...? Here is the code. A post mentioned doing the join earlier, i need to go back and regroup!

JOIN
INNER HOLDSORTED.HOLDSORT.PATIENT_ID AND HOLDSORTED.HOLDSORT.TRXFSCNBRKEY IN
HOLDSORTED TO UNIQUE HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID
AND HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY IN HTEMPPATFSCINFO TAG J0 AS J0
END
TABLE FILE HOLDSORTED
BY LOWEST HOLDSORTED.HOLDSORT.PATIENT_ID
BY LOWEST HOLDSORTED.HOLDSORT.TRXFSCNBRKEY
BY LOWEST HOLDSORTED.HOLDSORT.Invoice_Number
BY LOWEST J0.HTEMPPAT.COPAY_AMOUNT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

still no luck with the match either...I get an error on the BY FST.PATIENT_ID line...

TABLE FILE HTEMP
LIST
HTEMP.HTEMP.Group_Number
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Invoice_Number
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.CurrentFSC
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.PATIENT_ID
HTEMP.HTEMP.TRXFSCNBRKEY
ON TABLE HOLD AS HTEMPB FORMAT ALPHA
END
MATCH FILE HTEMPB
SUM
FST.HTEMPB.HTEMPB.Group_Number
FST.HTEMPB.HTEMPB.PAT_MRN
FST.HTEMPB.HTEMPB.PAT_NM
FST.HTEMPB.HTEMPB.Invoice_Number
FST.HTEMPB.HTEMPB.Date_Of_Service
FST.HTEMPB.HTEMPB.TRX_PostingDt
FST.HTEMPB.HTEMPB.CurrentFSC
BY FST.HTEMPB.HTEMPB.PATIENT_ID
BY FST.HTEMPB.HTEMPB.TRXFSCNBRKEY
BY LIST
RUN
FILE HTEMPPATFSCINFO
SUM
HTEMPPATFSCINFO.HTEMPPAT.COPAY_AMOUNT
BY HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID
BY HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY
AFTER MATCH HOLD AS HTEMPC OLD-AND-NEW
END
TABLE FILE HTEMPC
PRINT
HTEMPC.HTEMPC.PATIENT_ID
HTEMPC.HTEMPC.TRXFSCNBRKEY
HTEMPC.HTEMPC.LIST
HTEMPC.HTEMPC.Group_Number
HTEMPC.HTEMPC.PAT_MRN
HTEMPC.HTEMPC.PAT_NM
HTEMPC.HTEMPC.Invoice_Number
HTEMPC.HTEMPC.Date_Of_Service
HTEMPC.HTEMPC.TRX_PostingDt
HTEMPC.HTEMPC.CurrentFSC
HTEMPC.HTEMPC.TRXNBRFSCKEY
HTEMPC.HTEMPC.COPAY_AMOUNT
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

How about just swapping your two files around in your match?

quote:

MATCH FILE HTEMPPATFSCINFO
SUM
HTEMPPATFSCINFO.HTEMPPAT.COPAY_AMOUNT
BY HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID AS 'PATIENT_ID'
BY HTEMPPATFSCINFO.HTEMPPAT.TRXNBRFSCKEY AS 'TRXNBRFSCKEY'
RUN
FILE HTEMP
PRINT
HTEMP.HTEMP.Group_Number
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Invoice_Number
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.CurrentFSC
BY HTEMP.HTEMP.PATIENT_ID AS 'PATIENT_ID'
BY HTEMP.HTEMP.TRXFSCNBRKEY AS 'TRXNBRFSCKEY'

AFTER MATCH HOLD AS HTEMP6 whatever you're looking for
END

SUM on the first file will return one row per PATIENT_ID/TRXNBRFSCKEY
PRINT on the second file will return multiple rows per PATIENT_ID/TRXNBRFSCKEY
The MATCH will put the total COPAY_AMOUNT for each PATIENT_ID/TRXNBRFSCKEY against each row in the second file.

FOCAL POINT, I think MATCH FILE would be a good webex topic! ;-)

Well, it worked HOWEVER not at first. I noticed my second key values: TRXNBRFSCKEY were not spelled the same in each table nor the match. That also may have had a lot to do with. Funny that Webfocus did not throw an error.

Thank you all for your help. Hopefully I can build on this. I am going to go back an see if I can get the join to work too.

FYI...the MATCH now works...the JOIN however does not...if anyone is interested the code is below. In class I recall a general statement about NOT using a join with two hold tables so maybe it should not work. More likely its my inexperience and I have something set wrong.

SET ASNAMES = ON
TABLE FILE ANALYZER_INVOICE_VW
BY LOWEST ANALYZER_INVOICE_VW.PATIENT.PATIENT_ID
BY LOWEST ANALYZER_INVOICE_VW.TRANSACTION.TRX_FSC_Number AS 'TRXFSCNBRKEY'
BY LOWEST ANALYZER_INVOICE_VW.INVOICE.Invoice_Number
WHERE ANALYZER_INVOICE_VW.INVOICE.CurrentFSC NE '5';
ON TABLE HOLD AS HTEMP FORMAT ALPHA
END
TABLE FILE ANALYZERFACTS
BY LOWEST ANALYZERFACTS.REG_FSC_FOLLOWUP.PATIENT_ID
BY LOWEST ANALYZERFACTS.REGISTRATION_FSC.FINANCIAL_CLASS_NUMBER AS 'TRXFSCNBRKEY'
BY LOWEST ANALYZERFACTS.REG_FSC_FOLLOWUP.COPAY_AMOUNT
ON TABLE HOLD AS HTEMPPATFSCINFO FORMAT ALPHA
END
JOIN
INNER HTEMP.HTEMP.PATIENT_ID AND HTEMP.HTEMP.TRXFSCNBRKEY IN HTEMP TO UNIQUE
HTEMPPATFSCINFO.HTEMPPAT.PATIENT_ID AND HTEMPPATFSCINFO.HTEMPPAT.TRXFSCNBRKEY
IN HTEMPPATFSCINFO TAG J0 AS J0
END
TABLE FILE HTEMP
PRINT
HTEMP.HTEMP.PATIENT_ID
HTEMP.HTEMP.Invoice_Number
J0.HTEMPPAT.COPAY_AMOUNT
ON TABLE PCHOLD FORMAT HTML

END

quote:

I get an error on the BY FST.PATIENT_ID line...

That's a syntax issue -- delete the FST. operator in the BY lines.

btw, the join code above gets the right number of records...the copay is always blank though...

How are the fields

ANALYZER_INVOICE_VW.PATIENT.PATIENT_ID
ANALYZERFACTS.REG_FSC_FOLLOWUP.PATIENT_ID
ANALYZER_INVOICE_VW.TRANSACTION.TRX_FSC_Number
ANALYZERFACTS.REGISTRATION_FSC.FINANCIAL_CLASS_NUMBER

declared in the respective synonyms?

if the formats of corresponding JOIN fields are not identical, that could explain your results.