Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     MATCH EXTERNAL FILES

Read-Only Read-Only Topic
Go
Search
Notify
Tools
MATCH EXTERNAL FILES
 Login/Join
 
<Umar Farook S>
posted
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
 
Report This Post
Virtuoso
posted Hide Post
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.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Umar Farook S>
posted
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.
 
Report This Post
Virtuoso
posted Hide Post
Yes in both.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Umar Farook S>
posted
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...
 
Report This Post
Guru
posted Hide Post
Those 'excess' columns appear because you have asked for them in the print command.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
<Umar Farook S>
posted
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>,
 
Report This Post
<Umar Farook S>
posted
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
 
Report This Post
Guru
posted Hide Post
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.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
<Umar Farook S>
posted
I do not have acces to those files.
 
Report This Post
Virtuoso
posted Hide Post
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.?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Umar Farook S:
I do not have acces to those files.


Are you using the software without a license?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
<Umar Farook S>
posted
I have to print if any mismatches are present in any file on the key field
 
Report This Post
<Umar Farook S>
posted
I am working on my client side right now.This is the first time i am using webfocus for reporting.
 
Report This Post
Virtuoso
posted Hide Post
Umar,

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


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Umar Farook S>
posted
I will check it out.
Thanks Leah.
 
Report This Post
Silver Member
posted Hide Post
Mmm... match statments are tricky.

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


A message from the darks side, there is!
 
Posts: 39 | Registered: August 28, 2007Report This Post
<Umar Farook S>
posted
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
 
Report This Post
Virtuoso
posted Hide Post
Umar

I ask again....

What do you want to do....

why Match....

what result do you expect?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<Umar Farook S>
posted
I need to compare 2 HOLD files and display matching and non-matching rows based on key columns as report O/P.
 
Report This Post
Master
posted Hide Post
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.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
If you don't want your fields repeated, add NOPRINT to the BY statements:

BY COUNTRY NOPRINT


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
<Umar Farook S>
posted
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
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     MATCH EXTERNAL FILES

Copyright © 1996-2020 Information Builders