Hi folks,

I have been trying to change the format of my master files from:

 FILENAME=system_load, SUFFIX=FOC     , $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=AS_OF_DATE, ALIAS=AS_OF, USAGE=YYM, FIELDTYPE=I, $
  SEGMENT=SEG02, SEGTYPE=S10, PARENT=SEG01, $
    FIELDNAME=FCT_KEY, ALIAS=E01, USAGE=A7, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=DATA_ID, ALIAS=E02, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=SA_NAME, ALIAS=E03, USAGE=A20,
      MISSING=ON, $
    FIELDNAME=SA_KEY, ALIAS=E04, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=GT_NAME, ALIAS=E05, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=GT_KEY, ALIAS=E06, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=MR_NAME, ALIAS=E07, USAGE=A164,
      MISSING=ON, $
    FIELDNAME=MR_KEY, ALIAS=E08, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=SYS_NAME, ALIAS=E09, USAGE=A36,
      MISSING=ON, $
    FIELDNAME=SYS_KEY, ALIAS=E10, USAGE=A6,
      MISSING=ON, $
    FIELDNAME=CMS, ALIAS=E11, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=SRI, ALIAS=E12, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=EIDM, ALIAS=E13, USAGE=I11,
      MISSING=ON, $
    FIELDNAME=SRF, ALIAS=E14, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=EFDM, ALIAS=E15, USAGE=I11,
      MISSING=ON, $
    FIELDNAME=DESC1, ALIAS=E16, USAGE=A212,
      MISSING=ON, $
    FIELDNAME=DLC_VAL, ALIAS=E17, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=TOT_DLC, ALIAS=E18, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=COMMENTX, ALIAS=E19, USAGE=A300,
      MISSING=ON, $
    FIELDNAME=ICDR_DATE, ALIAS=E20, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=ICDR2_DATE, ALIAS=E21, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=FCDR_DATE, ALIAS=E22, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=SUBJECT_COLOUR, ALIAS=E23, USAGE=A3,
      MISSING=ON, $ 

To:

 FILENAME=system_load, SUFFIX=FOC     , $
  SEGMENT=SEG01, SEGTYPE=S1, $
    FIELDNAME=AS_OF_DATE, ALIAS=AS_OF, USAGE=YYM, FIELDTYPE=I, $
  SEGMENT=SEG02, SEGTYPE=S1, PARENT=SEG01, $
    FIELDNAME=SA_KEY, ALIAS=E04, USAGE=A5, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=SA_NAME, ALIAS=E03, USAGE=A20,
      MISSING=ON, $
  SEGMENT=SEG03, SEGTYPE=S1, PARENT=SEG02, $
    FIELDNAME=SYS_KEY, ALIAS=E10, USAGE=A6, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=SYS_NAME, ALIAS=E09, USAGE=A36,
      MISSING=ON, $
    FIELDNAME=CMS, ALIAS=E11, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=SRI, ALIAS=E12, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=EIDM, ALIAS=E13, USAGE=I11,
      MISSING=ON, $
    FIELDNAME=SRF, ALIAS=E14, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=EFDM, ALIAS=E15, USAGE=I11,
      MISSING=ON, $
    FIELDNAME=DESC1, ALIAS=E16, USAGE=A212,
      MISSING=ON, $
    FIELDNAME=ICDR_DATE, ALIAS=E20, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=ICDR2_DATE, ALIAS=E21, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=FCDR_DATE, ALIAS=E22, USAGE=DMYY,
      MISSING=ON, $
    FIELDNAME=SUBJECT_COLOUR, ALIAS=E23, USAGE=A3,
      MISSING=ON, $
  SEGMENT=SEG04, SEGTYPE=S2, PARENT=SEG03, $
    FIELDNAME=FCT_KEY, ALIAS=E01, USAGE=A7, FIELDTYPE=I,
      MISSING=ON, $
    FIELDNAME=DATA_ID, ALIAS=E02, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=GT_NAME, ALIAS=E05, USAGE=A3,
      MISSING=ON, $
    FIELDNAME=GT_KEY, ALIAS=E06, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=MR_NAME, ALIAS=E07, USAGE=A164,
      MISSING=ON, $
    FIELDNAME=MR_KEY, ALIAS=E08, USAGE=A5,
      MISSING=ON, $
    FIELDNAME=DLC_VAL, ALIAS=E17, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=TOT_DLC, ALIAS=E18, USAGE=I4,
      MISSING=ON, $
    FIELDNAME=COMMENTX, ALIAS=E19, USAGE=A300,
      MISSING=ON, $ 

Quick summary: I have evaluated the structure of my data and found that i need to create new segments in the master file. However when i change the matser file directly i get thrown an error: (FOC335) SEGNAME IN FILE DESCRIPTION NOT FOUND IN DATA: SEG03.

Could anyone advise me on the best way to change master files like this?

Many Thanks

This message has been edited. Last edited by: <Emily McAllister>,

Hi Jon,

Unfortunately you can not just include new segments by modifying the synonym (master file).

To achieve this you really need to hold the data temporarily before reloading into a new structure - especially if you need to retain current content.

There are several ways of achieving this and each would suit a different situation.

I would also suggest that you would need to change the procedure that is used to populate the table. If it is a transient table then this could be all you need to effect.

If you do not have sufficient knowledge within your team, it might be an idea to engage with your local professional services team?

T

Tony,

Thanks for the response, I have found a solution to the problem by using a multi select statement to build a new mas from the original

 TABLE FILE SYSTEM_LOAD1
-* Create Segment 1
SUM AS_OF_DATE 
BY AS_OF_DATE NOPRINT
-* Create Segment 2
SUM SA_KEY SA_NAME 
BY AS_OF_DATE NOPRINT
-* Create Segment 3
SUM 
SYS_KEY
SYS_NAME
CMS
SRI
EIDM
SRF
EFDM
DESC1
ICDR_DATE
ICDR2_DATE
FCDR_DATE
SUBJECT_COLOUR
BY AS_OF_DATE 
BY SA_KEY NOPRINT BY SA_NAME NOPRINT
-* Create Segment 4
PRINT
FCT_KEY
DATA_ID
GT_NAME
GT_KEY
MR_NAME
MR_KEY
DLC_VAL
TOT_DLC
COMMENTX
BY AS_OF_DATE 
BY SA_KEY NOPRINT BY SA_NAME NOPRINT BY SYS_KEY NOPRINT
ON TABLE HOLD AS TEST_TEST_TEST FORMAT FOCUS INDEX AS_OF_DATE SA_KEY SYS_KEY FCT_KEY
END
-RUN

CMD TYPE .\test_test_test.mas
-RUN 

quote:

need to change the procedure that is used to populate the table

Precisely what you needed!

T

Correct, you can use multi-verb statements to create FOCUS databases with multiple segments. It would be nice if we would have some control over the segment names generated thusly, but AFAIK that's not possible.

A few words of warning though: When you use such hierarchical files in joins, you can run into trouble joining multiple of them on fields that are not part of the top segment. Let that sink in... I'll try to explain.

That's important when you create FOCUS databases with a top segment that's a summary or aggregation of the contents on deeper levels (lower segments) in the hierarchy. Especially since with such databases it's rarely useful to join on fields in the summary.

Use case? Say you have a drill-down on field X in a top-level report, where there are all kinds of user-facing details about X (description, unit of measurement).
In a drill-down report, you typically want to keep such details around for use in a heading or some such, to provide the user context of what they are looking at.
When you drill down again from there, there are more details to keep around.

Putting that information in a high-level segment is attractive, as you only store the data once (it is a hierarchical database after all). Doing so puts your detail data in a lower-level segment. Where would you expect that your key fields and indexes are on? Right, no longer on the top level!

Say, for example, that with your table you would like to join SA_NAME to a similarly organised FOCUS file, where the equivalent of SA_NAME also isn't on the top-level. Perhaps you would like to join that file to the same file with data from last year.

I'd love to hear that this can actually be done, but I haven't found a way to make that work and the errors you get trying say as much that it's not possible.

You can also use REBUILD REORG. Its designed to do what your talking about. Its been a long time since i've had to use it though. Here is a snippet from the help file. Probably worth reading through the whole help file on it though.

quote:

Changing Data Source Structure: The REORG Subcommand
How to:

Use the REORG Subcommand


The REORG subcommand enables you to make a variety of changes to the Master File after data has been entered in the FOCUS data source. REBUILD REORG is a two-step procedure that first dumps the data into a temporary workspace and then reloads it under a new Master File.

You can use REBUILD REORG to:

Add new segments as descendants of existing segments.
Remove segments.
Add new data fields as descendants to an existing segment.
Note: The fields must be added after the key fields.

Remove data fields.
Change the order of non-key data fields within a segment. Key fields may not be changed.
Promote fields from unique segments to parent segments.
Demote fields from parent segments to descendant unique segments.
Index different fields.
Increase or decrease the size of an alphanumeric data field.
REBUILD REORG will not enable you to:

Change field format types (alphanumeric to numeric and vice versa, changing numeric format types).
Change the value for SEGNAME attributes.
Change the value for SEGTYPE attributes.
Change field names that are indexed.