Okay I have the following code: MATCH FILE Un_Fd BY ID_Nu...

[Russian Wyatt 2]

Okay I have the following code:

 

MATCH FILE Un_Fd

BY ID_Num

BY EmpName

BY HiringDate

BY AnnualHours

PRINT Position

PRINT ID_Num_Replace

PRINT EmpName_Replace

PRINT HiringDate2

PRINT TerminationDate2

PRINT AnnualWage2

PRINT AnnualHours2

RUN

FILE R_E_3

BY ID_Num

BY EmpName

BY HiringDate

BY AnnualHours

PRINT Position

AFTER MATCH HOLD OLD-NOR-NEW AS Un_Fd_2 FORMAT XFOCUS INDEX ID_Num

END

 

 

Im trying to merge the data from R_E_3 with Un_Fd, but every time I run it I get this error:

(FOC024) LIST OR PRINT MUST BE THE LAST VERB IN A MULTI-VERB REQUEST

Can someone show me what Im doing wrong

[David Beagan]

You are coding this like a multi-verb request which I assume was not your intent. Replace this

PRINT Position

PRINT ID_Num_Replace

PRINT EmpName_Replace

PRINT HiringDate2

PRINT TerminationDate2

PRINT AnnualWage2

PRINT AnnualHours2

 

with this

PRINT Position

ID_Num_Replace

EmpName_Replace

HiringDate2

TerminationDate2

AnnualWage2

AnnualHours2

[Russian Wyatt 2]

Thanks I knew it was something simple. Of course this exposed a flaw in my logic.

What I was trying to do was pull the records from R_E_3 that do not exist in Un_Fd based on ID_Num, EmpName, HiringDate, & AnnualHours. Ultimately I realized for my purposes I just needed it based on ID_Num and modified my code as such.

MATCH FILE Un_Fd

BY ID_Num

PRINT EmpName

HiringDate

AnnualHours

ID_Num_Replace

EmpName_Replace

HiringDate2

TerminationDate2

AnnualWage2

AnnualHours2

Position

RUN

FILE R_E_3

BY ID_Num

PRINT EmpName

HiringDate

AnnualHours

Position

AFTER MATCH HOLD OLD-OR-NEW AS Un_Fd_2 FORMAT XFOCUS INDEX ID_Num

END

 

Unfortunately, the result is that any duplicates are added to the final table as seg02.EmpName, seg02.HiringDate, So when I view the final table it looks as if the ID_Nums have been added but the rest of the data appears empty until I scroll all the way to the right to find the "seg02 columns that were added.

[Martin Yergeau]

Instead of PRINT use SUM in both places

[David Beagan]

Russian:

 

What I was trying to do was pull the records from R_E_3 that do not exist in Un_Fd based on ID_Num, EmpName, HiringDate, & AnnualHours.

 

 

That sounds like NEW-NOT-OLD

 

Maybe that fixes your issue If not, then

It looks like the XFOCUS format generates multisegment master. However if you just let it go to the default format then it is single segment. Maybe that fixes your issue

It sounds like what you are trying to do is

R_E_3 Left Outer Un_Fd

WHERE HiringDate2 IS MISSING

Have you ruled out using a WebFOCUS LEFT_OUTER JOIN rather than MATCH FILE

[Russian Wyatt 2]

NEW-NOT-OLD just gives me a table with only the unique items from R_E_3

OLD-OR-NEW gets the right data merge but it also creates the multi-segment table.

I tried removing the FORMAT XFOCUS INDEX ID_Num, but I still ended up with the multi-segmented table.

I tried using SUM instead of print and the only difference is it tried to SUM the position

Ive had no luck with Joins

[Martin Yergeau]

Hold the data from NEW-NOT-OLD just gives me a table with only the unique items from R_E_3.

Lets say you name it TEMP1

Then MORE the two files together

TABLE FILE Un_Fd

PRINT EmpName

HiringDate

AnnualHours

ID_Num_Replace

EmpName_Replace

HiringDate2

TerminationDate2

AnnualWage2

AnnualHours2

Position

BY ID_Num

MORE

FILE TEMP1

END

[Russian Wyatt 2]

Okay this works with a couple of caveats.

 

 

In my original post, the second file did not have all of the fields as the original, so it would give me an error about formatting issues on the Position field (last one in the main file). Once I added the missing fields (via DEFINE FILE) to the second file, it worked

 

 

for those like me who didnt know: this just displays the concatenated result but doesnt actually re-write the original table. I had to add the ON TABLE HOLD and put it prior to the MORE command (Im so used to putting it next to END, that I missed the slight subtly. So the end result was:

 

 

 

TABLE FILE Un_Fd

PRINT EmpName

HiringDate

AnnualHours

ID_Num_Replace

EmpName_Replace

HiringDate2

TerminationDate2

AnnualWage2

AnnualHours2

Position

BY ID_Num

ON TABLE HOLD AS Un_FD

MORE

FILE TEMP1

END