I have a report I found a bug. I am not sure how to fix it since I need 2 join work together. see below. there are 2 tables I need to join with both the Keyfield and stwk- the week. below is the code, without the stwk join, the data did not pull correctly:

table 1
DEFINE FILE APPOINTMENT_FACT
KEYFIELD/A200V = APPOINTMENT_FACT.REGION_NAME | APPOINTMENT_FACT.BUILDING_CODE | APPOINTMENT_FACT.CLINIC_NAME | APPOINTMENT_FACT.CLINIC_SUPERVISOR ;

STDT/MDYY = APPOINTMENT_DATE;
DAY_DOWK/I8YYMD = APPOINTMENT_DATE;
CLOSED_DT_DOW/A3=DOWK(DAY_DOWK, 'A3');
-*STWK/MDYY = IF CLOSED_DT_DOW EQ 'SAT' THEN (STDT - 6) ELSE ( DATEMOV (STDT, 'BOW') -2 );
STWK/MDYY = DATEMOV (STDT, 'BOW') -2 ;
STWK1/MDYY = DATEMOV (STDT, 'BOW') +4 ;
END


TABLE FILE APPOINTMENT_FACT
SUM CNT.MRN AS 'APPCNT'
BY KEYFIELD
BY HIGHEST 6 STWK
BY APPOINTMENT_FACT.REGION_NAME
BY APPOINTMENT_FACT.BUILDING_CODE
BY APPOINTMENT_FACT.CLINIC_NAME
BY APPOINTMENT_FACT.CLINIC_SUPERVISOR
BY STWK
ON TABLE HOLD AS HAPPDT FORMAT FOCUS INDEX KEYFIELD
END

table 2

DEFINE FILE FACT_CSRERRORRATE
KEYFIELD/A200V = FACT_CSRERRORRATE.REGION_NAME | FACT_CSRERRORRATE.BUILDING_CODE |FACT_CSRERRORRATE.CLINIC_NAME |FACT_CSRERRORRATE.CLINIC_SUPERVISOR;
CSR_DATE1/YYMD = HDATE(CSR_DATE, 'YYMD');
STDT/MDYY = CSR_DATE1;
DAY_DOWK/I8YYMD = CSR_DATE1;
CLOSED_DT_DOW/A3=DOWK(DAY_DOWK, 'A3');
STWK/MDYY = DATEMOV (STDT, 'BOW') -2 ;
STWK1/MDYY = DATEMOV (STDT, 'BOW') +4 ;
END

TABLE FILE FACT_CSRERRORRATE
SUM CNT.FACT_CSRERRORRATE_ID AS 'ERRORCNT'
BY KEYFIELD
BY HIGHEST 6 STWK
BY FACT_CSRERRORRATE.REGION_NAME
BY FACT_CSRERRORRATE.BUILDING_CODE
BY FACT_CSRERRORRATE.CLINIC_NAME
BY FACT_CSRERRORRATE.CLINIC_SUPERVISOR
BY STWK

ON TABLE HOLD AS HERROR1 FORMAT FOCUS INDEX KEYFIELD
END

JOIN KEYFIELD IN HERROR1 TO
KEYFIELD IN HAPPDT TO AS X3
-RUN
END

JOIN STWK IN HERROR1 TO
STWK IN HAPPDT TO AS X4

it is worked joining by keyfield, but I need to join by stwk too -each week of data got matched

above code does not work. the error said the stwk is not index.

any idea on how to fix it

thanks a bunch

grace

This message has been edited. Last edited by: <Kathryn Henning>,

JG,
When joining FOCUS files you can only use 1 index field. So append STWK to your KRYFIELD:

  
DEFINE FILE APPOINTMENT_FACT

STDT/MDYY = APPOINTMENT_DATE;
DAY_DOWK/I8YYMD = APPOINTMENT_DATE;
CLOSED_DT_DOW/A3=DOWK(DAY_DOWK, 'A3');
-*STWK/MDYY = IF CLOSED_DT_DOW EQ 'SAT' THEN (STDT - 6) ELSE ( DATEMOV (STDT, 'BOW') -2 );
STWK/MDYY = DATEMOV (STDT, 'BOW') -2 ;
STWK1/MDYY = DATEMOV (STDT, 'BOW') +4 ;
ASTWK/A8MDYY=STWK;
KEYFIELD/A200V = APPOINTMENT_FACT.REGION_NAME | APPOINTMENT_FACT.BUILDING_CODE | APPOINTMENT_FACT.CLINIC_NAME | APPOINTMENT_FACT.CLINIC_SUPERVISOR |
ASTWK ;

END


TABLE FILE APPOINTMENT_FACT
SUM CNT.MRN AS 'APPCNT'
BY KEYFIELD
BY HIGHEST 6 STWK
BY APPOINTMENT_FACT.REGION_NAME
BY APPOINTMENT_FACT.BUILDING_CODE
BY APPOINTMENT_FACT.CLINIC_NAME
BY APPOINTMENT_FACT.CLINIC_SUPERVISOR
BY STWK
ON TABLE HOLD AS HAPPDT FORMAT FOCUS INDEX KEYFIELD
END

table 2

DEFINE FILE FACT_CSRERRORRATE
CSR_DATE1/YYMD = HDATE(CSR_DATE, 'YYMD');
STDT/MDYY = CSR_DATE1;
DAY_DOWK/I8YYMD = CSR_DATE1;
CLOSED_DT_DOW/A3=DOWK(DAY_DOWK, 'A3');
STWK/MDYY = DATEMOV (STDT, 'BOW') -2 ;
STWK1/MDYY = DATEMOV (STDT, 'BOW') +4 ;
ASTWK/A8MDYY=STWK;
KEYFIELD/A200V = FACT_CSRERRORRATE.REGION_NAME | FACT_CSRERRORRATE.BUILDING_CODE |FACT_CSRERRORRATE.CLINIC_NAME |FACT_CSRERRORRATE.CLINIC_SUPERVISOR
|ASTWK;
END

TABLE FILE FACT_CSRERRORRATE
SUM CNT.FACT_CSRERRORRATE_ID AS 'ERRORCNT'
BY KEYFIELD
BY HIGHEST 6 STWK
BY FACT_CSRERRORRATE.REGION_NAME
BY FACT_CSRERRORRATE.BUILDING_CODE
BY FACT_CSRERRORRATE.CLINIC_NAME
BY FACT_CSRERRORRATE.CLINIC_SUPERVISOR
BY STWK

ON TABLE HOLD AS HERROR1 FORMAT FOCUS INDEX KEYFIELD
END

JOIN KEYFIELD IN HERROR1 TO
 KEYFIELD IN HAPPDT TO AS X3
END
-RUN

MATCH FILE.

So good it's almost disconcerting to use.

I thought about adding the STWK into keyfield, but I can't, cause I use key field to join 5 different tables and one of them does not have stwk.
how to do match file? any example of the code?

It's in your help file!

quote:

You can merge two or more data sources, and specify which records to merge and which to sort out, using the MATCH command. The command creates a new data source (a HOLD file), into which it merges fields from the selected records. You can report from the new data source and use it as you would use any other HOLD file. The merge process does not change the original data sources. For more information on HOLD files, see Saving and Reusing Your Report Output

You select the records to be merged into the new data source by specifying sort fields in the MATCH command. You specify one set of sort fields (using the BY phrase), for the first data source, and a second set of sort fields for the second data source. The MATCH command compares all sort fields that have been specified in common for both data sources, and then merges all records from the first data source whose sort values match those in the second data source into the new HOLD file. You can specify up to 32 sort sets. This includes the number of common sort fields.

In addition to merging data source records that share values, you can merge records based on other relationships. For example, you can merge all records in each data source whose sort values are not matched in the other data source. Yet another type of merge combines all records from the first data source with any matching records from the second data source.

You can merge up to 16 sets of data in one Match request. For example, you can merge different data sources, or data from the same data source.


MATCH FILE file1
.
.
.
RUN
FILE file2
.
.
.
[AFTER MATCH merge_phrase]
RUN
FILE file3
.
.
.
[AFTER MATCH merge_phrase]
END


MATCH FILE EDUCFILE
SUM COURSE_CODE
BY EMP_ID
RUN
FILE EMPLOYEE
SUM LAST_NAME AND FIRST_NAME
BY EMP_ID BY CURR_SAL
AFTER MATCH HOLD OLD-OR-NEW
END

we need LIKE buttons on this forum thingy.
i wanna LIKE John Edwards' answer. don't you?

I was thinking the same thing. There's been several occasions when I wanted to give someone a thumbs up and there's no way to do it short of responding in text.

Except sue match file. are there any other options? I never use match before. just explore all the options thank you!

I tried the match as below:. it did not work. gave me ONE junk records result

MATCH FILE HERROR1
SUM ERRORCNT
BY KEYFIELD
BY STWK
RUN

FILE HAPPDT
SUM APPCNT/D6
BY KEYFIELD
BY STWK
AFTER MATCH HOLD OLD-OR-NEW
END

TABLE FILE HOLD
PRINT *
END
-EXIT

You can always use a WHERE-Based JOIN:

JOIN FILE HERROR1 AT KEYFIELD TAG ERR TO ALL
     FILE HAPPDT  AT KEYFIELD TAG APP AS JW1
  WHERE ERR.KEYFIELD EQ APP.KEYFIELD;
  WHERE ERR.STWK EQ APP.STWK;
END

What about one of these for Thumbs Up? If you are using an email feed as I am you may have to do the download pictures thing. I got these from the emoticons tab in the reply tool.

OR:

The where based join worked like majec..
the counts are matching very well with the week and the keyfield. thank you so much for helping. I really appreciated!