Jump to content

I have 3 datasets with the same structure and a common index...


Russian Wyatt 2

Recommended Posts

I have 3 datasets with the same structure and a common index field (LinkID). I also have another dataset that has a different structure but contains the same index field as the ones above.

Im trying to use the MATCH command to merge these into a single segment datafile, but seem to be missing something.

MATCH FILE Company1

BY LinkID

PRINT LastName FirstName HiringDate

MORE

FILE Company2

MORE

FILE Company3

RUN

FILE Wages

BY LinkID

PRINT wage1 wage2 wage3 wage4 comments

AFTER MATCH HOLD OLD-OR-NEW AS hrdata FORMAT XFOCUS INDEX LinkID

 

Am I doing this correctly

After merging the files, I was going to create a form in which the user could update the last set of fields and still have it connected by the LinkID, but I keep getting a two segmented file.

Link to comment
Share on other sites

I do not have easy access to any of my old manuals that I used when using MATCH

I used to use it extensively

I may be wrong, but I believe you cannot mix and match the use of Match and MORE

Use TABLE FILE and more and get the output you want. Then use Match of the combined files and the File Wages.

This is a educated guess based on what I remember. I may be wrong

Link to comment
Share on other sites

Yes, looks like mixing MATCH and MORE.

Here is a quick working example to show how it is done:

MATCH FILE GGSALES

SUM UNITS

BY SEQ_NO

RUN

FILE GGSALES

SUM DOLLARS

BY SEQ_NO

RUN

FILE GGSALES

SUM BUDUNITS

BY SEQ_NO

AFTER MATCH HOLD OLD-OR-NEW AS GGHOLD

END

 

TABLE FILE GGHOLD

PRINT *

END

 

Documentation is here:

Creating Reports With TIBCO WebFOCUS Language - Ch. 15 Merging Data Sources

Link to comment
Share on other sites

On second look, Im thinking you are wanting to union join Company1, Company2 and Company3 and then join those to Wages. Turns out you can use MATCH and MORE together.

SET PAGE=NOLEAD

-* Make up three files to work with. ------------------

TABLE FILE employee

"emp1"

SUM LAST_NAME FIRST_NAME CURR_JOBCODE

BY EMP_ID

WHERE CURR_JOBCODE IS 'A07'

ON TABLE HOLD AS emp1

END

PCHOLD FORMAT HTML

 

TABLE FILE employee

"emp2"

SUM LAST_NAME FIRST_NAME CURR_JOBCODE

BY EMP_ID

WHERE CURR_JOBCODE IS 'B02'

ON TABLE HOLD AS emp2

END

PCHOLD FORMAT HTML

 

TABLE FILE employee

"emp3"

SUM LAST_NAME FIRST_NAME CURR_JOBCODE

BY EMP_ID

WHERE CURR_JOBCODE IS 'B04'

ON TABLE HOLD AS emp3

END

PCHOLD FORMAT HTML

-*-----------------------------------------------------

 

-* Use MATCH FILE with MORE to create the final result.

MATCH FILE emp1

PRINT FIRST_NAME LAST_NAME

BY EMP_ID

MORE

FILE emp2

MORE

FILE emp3

RUN

FILE employee

PRINT CURR_SAL SKILL_DESC

BY EMP_ID

AFTER MATCH HOLD OLD-AND-NEW AS emphold

END

 

TABLE FILE emphold

"</1 Final result of MATCH FILE / MORE"

PRINT *

END

 

This works. I assume in yours you had an END statement. You would need a final TABLE FILE to see the result.

Link to comment
Share on other sites

Thanks

I did include the final END statement in my code. I just missed adding to the post. Also in order to do the next step I needed the FORMAT XFOCUS

This creates the dataset with two segments in it and I had believed that was my problem. My next step was to create a form using the Maintain data: New with Update Assist

However, I kept getting the message: Error Parsing MASTER File (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HRDATA

I thought this was because the database was in two segments instead of one, I guess my problem is something else.

Link to comment
Share on other sites

Thanks for all the help

My final code for merging the multiple company data sets and wage data set into a single segment data file looks like this:

-* merge multiple data files into a single multi-segment data file

-SET &ECHO=ALL;

APP HOLD scenario_1

MATCH FILE Company1

PRINT LastName FirstName HiringDate

BY LinkID

MORE

FILE Company2

MORE

FILE Company3

RUN

FILE Wages

PRINT wage1 wage2 wage3 wage4 comments

BY LinkID

AFTER MATCH HOLD OLD-AND-NEW AS hrdata_preflat FORMAT XFOCUS INDEX LinkID

END

 

-* flatten the multi-segment data file into a single segment file

TABLE FILE hrdata_preflat

BY LinkID

SUM LastName FirstName HiringDate wage1 wage2 wage3 wage4 comments

ON TABLE HOLD AS hrdata FORMAT XFOCUS INDEX LinkID

END

 

This gives me a final data set (hrdata) that works for reporting via appstudio or infoassist.

Ill spend a bit more time plain with maintain to make sure I understand what my issue is on that front before posting again.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...