Hi
I am trying to match 2 HOLD files to get all the non-matching rows.
I have 5 columns.Any mismatch in any one of these columns will result in MIS_MATCH of the particular rows in 2 files.
here is my code.Please help me out.
::CODE::
MATCH FILE QMTRADEONE
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
RUN
FILE QMTRADETWO
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
AFTER MATCH HOLD OLD-NOR-NEW
END
-RUN
-*
TABLE FILE HOLD
PRINT *
END
-EXIT

Whenever you want to match, you have to use all the fields you want to match in a by field. I've never used the OLD-NOR-NEW output,so any one else have additional comments go for it.

I need to match 5 fields.So i will give 5 fields in by field.
I have to do this for both the MATCH FILE am i right.
I am doing OLD-NOR-NEW bcos i need the non-matching coloumns from both the input files.

Yes in both.

Hi Leah,
when i run the below code i get output like this
MATCH FILE QMTRADEONE
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
RUN
FILE QMTRADETWO
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
AFTER MATCH HOLD OLD-OR-NEW
END
-RUN
-*
TABLE FILE HOLD
PRINT *
END
-EXIT

::Ouptut::
PAGE 1

MMRefNumber TradeDate Portfolio NotionalAmt TradeCounterParty TradeDate Portfolio NotionalAmt TradeCounterParty AssetSubType AssetType TradeDate Portfolio NotionalAmt TradeCounterParty AssetSubType AssetType
03/11/05 ACMLUL 4,500,000.00 CITIGROUP FP 03/11/05 ACMLUL 4,500,000.00 CITIGROUP FP Vanilla Swap .00
04/11/05 ACMLUL 4,500,000.00 CITIGROUP FP 04/11/05 ACMLUL 4,500,000.00 CITIGROUP FP Vanilla Swap .00
12/01/05 ACMLUL 6,000,000.00 BK OF MONTREAL 12/01/05 ACMLUL 6,000,000.00 BK OF MONTREAL Currency Swap .00
02/27/06 AGIASPD 1,000,000.00 MERRILL CAP SRV 02/27/06 AGIASPD 1,000,000.00 MERRILL CAP SRV Lnr Cap Purch Cap/Floor


when i include all the fileds to be compared in BY field i get excess columns like Trade Date, Portfolio, NotionalAmt,TradeCounterParty...

Those 'excess' columns appear because you have asked for them in the print command.

Hi I have altered my code but the values in first match file is displayed twice and the values in second file are displayed once.I do not know where i have gone wrong whether in BY fields or in PRINT fields.
Since 5 columns are key coloumns i have included all the 5 in BY fields.
Please help me out on this problem.


::CODE::
-* File qmtrade2csv.fex
FILEDEF QMTRADE1 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE1.CSV
FILEDEF QMTRADE2 DISK \\IMNT14\SHARED\TRANSFER\NEW_TRADE2.CSV
SET ASNAMES=ON
TABLE FILE QMTRADE1
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
ON TABLE HOLD AS QMTRADEONE
END
-RUN
-*
-*TABLE FILE QMTRADEONE
-*COUNT *
-*END
-*-EXIT

DEFINE FILE QMTRADE2
END
-*
TABLE FILE QMTRADE2
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
ON TABLE HOLD AS QMTRADETWO
END
-RUN
-*
MATCH FILE QMTRADEONE
PRINT
TradeDate
Portfolio
NotionalAmt
TradeCounterParty
AssetSubType
AssetType
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
RUN
FILE QMTRADETWO
PRINT
MMRefNumber AS N1
TradeDate AS N2
Portfolio AS N3
NotionalAmt AS N4
TradeCounterParty AS N5
AssetSubType AS N6
AssetType AS N7
BY MMRefNumber
BY TradeDate
BY Portfolio
BY NotionalAmt
BY TradeCounterParty
AFTER MATCH HOLD AS NEWHOLD OLD-OR-NEW
END
-RUN
-*

DEFINE FILE NEWHOLD
DELIM/A1 = '|';
END
-RUN
-*
TABLE FILE NEWHOLD
HEADING
"COMPARE OF TWO CSV FILES"
"---- FILE1 ---------------------------- <65 -----------------------FILE2 --------"

PRINT
MMRefNumber AS 'MM,Ref,Number'
TradeDate AS 'Trade,Date'
Portfolio AS 'Port,folio'
NotionalAmt AS 'Notional,Amt'
TradeCounterParty AS 'Trade,Counter,Party'
AssetSubType AS 'Asset,Sub,Type'
AssetType AS 'Asset,Type'
DELIM AS ' ' IN +4
N1
N2
N3
N4
N5
N6
N7
BY MMRefNumber NOPRINT
BY TradeDate NOPRINT
BY Portfolio NOPRINT
BY NotionalAmt NOPRINT
BY TradeCounterParty NOPRINT
BY N1 NOPRINT
BY N2 NOPRINT
BY N3 NOPRINT
BY N4 NOPRINT
BY N5 NOPRINT
ON TABLE NOTOTAL

This message has been edited. Last edited by: <Umar Farook S>,

Continutaion of previous code


ON TABLE SET EMPTYREPORT ON
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='LEGAL',
LEFTMARGIN=0.100000,
RIGHTMARGIN=0.100000,
TOPMARGIN=0.100000,
BOTTOMMARGIN=0.100000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,

::OUTPUT::

AEO019049 07/31/01 MASIAE 25,000.00 EMBEDDED Call Purchased Option| .00
AEO019049 07/31/01 MASIAE 25,000.00 EMBEDDED |AEO019049 07/31/01 MASIAE 25,000.00 EMBEDDED Call Purchased Option
AEO019055 04/09/02 MASIAE 20,000.00 EMBEDDED Call Purchased Option| .00
AEO019055 04/09/02 MASIAE 20,000.00 EMBEDDED |AEO019055 04/09/02 MASIAE 20,000.00 EMBEDDED Call Purchased Option
AEO019056 12/06/01 MASIAE 25,000.00 EMBEDDED Call Purchased Option| .00
AEO019056 12/06/01 MASIAE 25,000.00 EMBEDDED |AEO019056 12/06/01 MASIAE 25,000.00 EMBEDDED Call Purchased Option

When you put a field name in a BY or PRINT (or SUM or WRITE) statement, the data in that field will be sent to the output data set.

You might want to search the Technical Documentation link on the upper right of this screen for additional infomation on the MATCH command.

I do not have acces to those files.

Umar

You get what you ask for....
But I'm not sure if you know what this match process is doing.
I think that you want to compare the first and the second file and print the changes of the NON keyfield, is that correct.?

quote:

Originally posted by Umar Farook S:
I do not have acces to those files.

Are you using the software without a license?

I have to print if any mismatches are present in any file on the key field

I am working on my client side right now.This is the first time i am using webfocus for reporting.

Umar,

Someone at the client site should have access to the documentation or is registered to access the documentation on line.

I will check it out.
Thanks Leah.

Mmm... match statments are tricky.

you need to read up on this or you could guess by doing a lot of trial and error.

i am back with MATCH problem
::CODE::
-* File DTCCtoMurex1.fex
-* 10/22/2007 - UMAR - New Report.Produces Compare of DTCC to MUREX inventory for last 30 Days
-**************************************************************************************************-
-SET &ECHO=ALL;
APP PREPENDPATH Murex
FILEDEF DTCC1 DISK \\IMNT14\SHARED\TRANSFER\DTCC.CSV
SET ASNAMES=ON
SET NULL=ON
SET NODATA = ' '
SET DEFCENT = 20
-RUN
-*
TABLE FILE DTCC1
PRINT
ProdT
TrefNr AS KEY1
CID
CONDate
TDate
TSupNr AS KEY2
FRPBuy
Buyname
Selname
Notion
BY TrefNr NOPRINT
ON TABLE HOLD AS DTCCNEW
END
-*

-*
DEFINE FILE DTCCNEW
NAME/A5 = IF FRPBuy = CID THEN 'Long' ELSE 'Short';
CDXCDS/A4 = IF ProdT = 'CreditDefaultSwapIndex' THEN 'CDX'
ELSE IF ProdT = 'CreditDefaultSwapShort' THEN 'CDS'
ELSE ' ';
ASSET_SUB/A10 = CDXCDS | NAME;
Count/A100 = IF FRPBuy = CID THEN Buyname ELSE Selname;
Legal/A100 = IF FRPBuy = CID THEN Selname ELSE Buyname;
NotionalDTCC/D18.2B = Notion;
END
-*
TABLE FILE DTCCNEW
PRINT
Count
Legal
ASSET_SUB
KEY1
CONDate
TDate
KEY2
NotionalDTCC
BY KEY1 NOPRINT
ON TABLE HOLD AS DTCCNEW1
END
-*

-*
DEFINE FILE DTCCNEW1
LEGAL_SUBSTR/A15 = SUBSTR(100, Legal, 1, 15, 15, LEGAL_SUBSTR);
COUNT_SUBSTR/A15 = SUBSTR(100, Count, 1, 15, 15, COUNT_SUBSTR);
END
-*
TABLE FILE DTCCNEW1
PRINT
LEGAL_SUBSTR
COUNT_SUBSTR
ASSET_SUB
KEY1
CONDate
TDate
KEY2
NotionalDTCC
-*BY KEY1 NOPRINT
ON TABLE HOLD AS DTCCNEW2 FORMAT FOCUS INDEX KEY1 KEY2
END
-*

-*
-DEFAULT &OracleConnect = 'ora025';

ENGINE SQLORA SET SERVER &OracleConnect
ENGINE SQLORA

SELECT
ASSET_ID,
CAST(DTCC_ID AS VARCHAR(30)) AS KEY1,
CAST(DTCC_TRADE_ID AS VARCHAR(30)) AS KEY2,
ASSET_TYPE,
ASSET_SUB_TYPE,
LEGAL_ENTITY_LONG_NAME,
COUNTERPARTY_LONG_NAME,
PORTFOLIO_SUB_SEGMENT AS PORTFOLIO,
TRADE_DATE,
RECEIVE_CONFIRMATION_DATE,
NOTIONAL_AMOUNT,
TRADER_COMMENT,
CONFIRMATION_COMMENT
FROM MurexODS.V_EXP_DTCC_TRADE_VERIFICATION
;
-*

TABLE FILE SQLOUT
PRINT
ASSET_ID
KEY1
KEY2
ASSET_TYPE
ASSET_SUB_TYPE
COUNTERPARTY_LONG_NAME
LEGAL_ENTITY_LONG_NAME
PORTFOLIO
TRADE_DATE
RECEIVE_CONFIRMATION_DATE
NOTIONAL_AMOUNT
TRADER_COMMENT
CONFIRMATION_COMMENT
-*BY KEY1 NOPRINT
ON TABLE HOLD AS MUREX1
END
-*
-*
DEFINE FILE MUREX1
RUNDATE/MDYY = &MDYY;
YESTERDAY/MDYY = DATEADD(RUNDATE,'D',-30);
END
-*
TABLE FILE MUREX1
PRINT
ASSET_ID
KEY1
KEY2
ASSET_TYPE
ASSET_SUB_TYPE
COUNTERPARTY_LONG_NAME
LEGAL_ENTITY_LONG_NAME
PORTFOLIO
TRADE_DATE
RECEIVE_CONFIRMATION_DATE
NOTIONAL_AMOUNT
TRADER_COMMENT
CONFIRMATION_COMMENT
RUNDATE
YESTERDAY
-*WHERE TRADE_DATE GT YESTERDAY AND TRADE_DATE LT RUNDATE
-*BY KEY1 NOPRINT
ON TABLE HOLD AS MUREX2 FORMAT FOCUS INDEX KEY1 KEY2
END
-*

-*
MATCH FILE DTCCNEW2
PRINT
LEGAL_SUBSTR
COUNT_SUBSTR
ASSET_SUB
KEY1
CONDate
TDate
KEY2
NotionalDTCC
BY KEY1 NOPRINT
BY KEY2 NOPRINT
RUN
FILE MUREX2
PRINT
ASSET_ID
KEY1
KEY2
ASSET_TYPE
ASSET_SUB_TYPE
COUNTERPARTY_LONG_NAME
LEGAL_ENTITY_LONG_NAME
PORTFOLIO
TRADE_DATE
RECEIVE_CONFIRMATION_DATE
NOTIONAL_AMOUNT
TRADER_COMMENT
CONFIRMATION_COMMENT
BY KEY1 NOPRINT
BY KEY2 NOPRINT
AFTER MATCH HOLD AS DTCCMUREX OLD-AND-NEW
END
-RUN
-*
TABLE FILE DTCCMUREX
PRINT *
END
-EXIT
-*
AFTER MATCH DOESNOT WORK. I GET ALL THE DATAS WHATEVER IS THE MATCH CONDITION

Umar

I ask again....

What do you want to do....

why Match....

what result do you expect?

Also, if you have access to this site to post questions, you have access to the documentation. From the tech support home page, go to the documentation section. You are looking for the language manual, "Creating Reports with the WebFOCUS Language", or go to your Developer Studio \bin directory and look for wfcrlang.pdf.

I need to compare 2 HOLD files and display matching and non-matching rows based on key columns as report O/P.

You want to hold OLD-OR-NEW. OLD-AND-NEW will give you only rows that occur in both files. OLD-OR-NEW will give you rows that occur in both or either.

If you don't want your fields repeated, add NOPRINT to the BY statements:

BY COUNTRY NOPRINT

Hi bright,

OLD-AND-NEW = will give only matching rows from both files

OLD-OR-NEW = will give all the rows irrespective of matching or not matching