I want to compare two columns of data to find matching values. The issue is that the values are not in the same row. I have looked at the Recursive Join and it pulls back all records regardless of a match.
Is the Recursive Join the correct approach? Below I am using the Match command to join two hold files and then I want to find matching values in fields 'IHMSTPO' and 'IHPONO'.
MATCH FILE POHEADER BY IHMSTPO BY IHPONO BY DTTRK# RUN FILE POHEADER2 BY IHMSTPO BY IHPONO BY DTTRK# AFTER MATCH HOLD AS PO_DRPSHP OLD-OR-NEW END
JOIN IHMSTPO.TAG G1 IN PO_DRPSHP TAG G1 TO IHPONO.TAG G2 IN PO_DRPSHP TAG 2 AS RECURSIV END
TABLE FILE PO_DRPSHP BY IHMSTPO BY IHPONO BY DTTRK# ON TABLE PCHOLD FORMAT EXL2K ENDThis message has been edited. Last edited by: Kerry,
WF 8.1.05, Windows 10 Databases: MS SQL 2012, Output Formats: HTML, Excel, PDF
July 13, 2009, 04:16 PM
Francis Mariani
Perhaps the problem is with which merge type you're using.
OLD-OR-NEW - specifies that all records from both the old data source and the new data source appear in the HOLD file. This means that all data is retrieved regardless if matching rows were found.
Take a look at the documentation, Creating Reports With WebFOCUS Language > Merging Data Sources > MATCH Processing > Reference: Merge Phrases:
OLD-OR-NEW specifies that all records from both the old data source and the new data source appear in the HOLD file. This is the default if the AFTER MATCH line is omitted.
OLD-AND-NEW specifies that records that appear in both the old and new data sources appear in the HOLD file.. (The intersection of the sets.)
OLD-NOT-NEW specifies that records that appear only in the old data source appear in the HOLD file.
NEW-NOT-OLD specifies that records that appear only in the new data source appear in the HOLD file.
OLD-NOR-NEW specifies that only records that are in the old data source but not in the new data source, or in the new data source but not in the old, appear in the HOLD file (the complete set of non-matching records from both data sources).
OLD specifies that all records from the old data source, and any matching records from the new data source, are merged into the HOLD file.
NEW specifies that all records from the new data source, and any matching records from the old data source, are merged into the HOLD file.
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
July 13, 2009, 04:19 PM
Microfich
One more hint:
MATCH 'BY' fields determine which columns are being MATCHed.
JOIN IHMSTPO.TAG G1 IN PO_DRPSHP TAG G1 TO IHPONO.TAG G2 IN PO_DRPSHP TAG 2 AS RECURSIV END
TABLE FILE PO_DRPSHP BY IHMSTPO BY IHPONO BY DTTRK# ON TABLE PCHOLD FORMAT EXL2K END
One more thing to bear in mind is that because you are using the same table you really should get into the habit of specifying from which table the columns within your report originate - especially as you have already coded the TAG components of the JOIN. As your code is you will get the entries from the first component in the JOIN.
As you have merged your data in the initial MATCH you amy only require a table from PO_DRPSHP with either DEFINE or COMPUTE fields to identify differences.
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
July 14, 2009, 09:22 AM
Howell
The data coming from the initial match process is fine. I have to combine those two hold files before the final test. In the hold file PO_DRPSHP I need to identify rows where values in column A match column B. Below is a small sample of the data.
The final result is I need to eliminate any rows where the value in column A is also in column B and column B equals 0.
WF 8.1.05, Windows 10 Databases: MS SQL 2012, Output Formats: HTML, Excel, PDF
July 14, 2009, 10:30 AM
Tony A
So, WHERE NOT ([column A] EQ [column B] OR [column B] EQ 0) ?
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
July 15, 2009, 06:06 AM
Danny-SRL
Howell,
If I understand correctly, and using your example, you would want to get rid of the lines where A is 1457065 and B is 0, because you have lines where A is 1457063 and B is 1457065.
I loaded your data (note that I changed the first line where A is 1457065):
-* File Howell1.fex
-SET &ECHO=ON;
CREATE FILE HOWELL
MODIFY FILE HOWELL
MATCH A
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA
A=1457054, B=0,$
A=1457054, B=0,$
A=1457055, B=0,$
A=1457063, B=1457065,$
A=1457063, B=1457065,$
A=1457063, B=1457065,$
A=1457064, B=0,$
A=1457065, B=1,$
A=1457065, B=0,$
A=1457065, B=0,$
END
? FILE HOWELL
And then I extracted the lines you needed:
-* File HOWELL2.fex
SET ALL = PASS
JOIN A IN HOWELL TAG F TO ALL B IN HOWELL TAG T AS J
TABLE FILE HOWELL
PRINT F.A AS FA
F.B AS FB
WHERE F.A NE T.B OR F.B NE 0;
END