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
END

This message has been edited. Last edited by: Kerry,

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.

One more hint:

MATCH 'BY' fields determine which columns are being MATCHed.

quote:

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

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.


A B
1457054 0
1457054 0
1457055 0
1457063 1457065
1457063 1457065
1457063 1457065
1457064 0
1457065 0
1457065 0
1457065 0

So, WHERE NOT ([column A] EQ [column B] OR [column B] EQ 0) ?

T

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.

A       B 
1457054 0
1457054 0
1457055 0
1457063 1457065
1457063 1457065
1457063 1457065
1457064 0
1457065 0
1457065 0
1457065 0

Using your example data, I created a FOCUS file:

  
FILENAME=HOWELL,SUFFIX=FOC,
    DATASET=C:\IBI\APPS\FOCALPOINT\HOWELL.FOC
 SEGNAME=HOWELL,SEGTYPE=S1
  FIELDNAME=A,A,I9,$
  FIELDNAME=B,B,I9,INDEX=I,$

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

And the result:

  
FA      FB 
1457054 0 
1457054 0 
1457055 0 
1457063 1457065 
1457063 1457065 
1457063 1457065 
1457064 0 
1457065 1 

This message has been edited. Last edited by: Danny-SRL,

Danny you are correct.

Howell, did it work for you?