I need help with what should have been a easy problem to solve.

I have a next 5 day schedule of employees grouped by date by employee in a hold file. I need to know when one of the employees is not on the schedule for any given day.

Example:
DATE EMP
1 aaaaaa
1 bbbbbb
1 cccccc
2 aaaaaa
2 cccccc

I would like my output to be: (or someting like this)
DATE EMP COUNT
1 aaaaaa 1
1 bbbbbb 1
1 cccccc 1
2 aaaaaa 1
2 cccccc 1
2 bbbbbbb 0

I have the DATES and EMPLOYEES in their own hold files.

I have read several posts on JOINS, MATCH, and LOOPS (and a combination of such). Non of them really answered my question or even got me close. Does anyone have any suggetions or guidance?

Thanks

This message has been edited. Last edited by: Ken DeNoma,

Roughly speaking, I would
* hold the list of distinct dates,
* hold the list of distinct employees,
* use match file to merge old=[cartesion product of the two] by date by emp with new=[the date/emp data file] by date by emp.

If you hold OLD-NOT-NEW, you get the unscheduled employees.

I have been told that MATCH would work. I'm not getting the syntax down right I'm sure.

Here is what I have:

-*LIST OF DST EMPLOYEES(AGENTS)
TABLE FILE SCHEDULE1
SUM CNT.AGENT_ID NOPRINT
BY AGENT_ID
ON TABLE HOLD AS DST_AGENTS
END
-RUN
-*-EXIT
-*LIST OF DST DATES
TABLE FILE SCHEDULE1
SUM CNT.DATE NOPRINT
BY DATE
ON TABLE HOLD AS DST_DATES
END
-RUN
-*-EXIT
-*MATCH STATEMENT TO JOIN?
MATCH FILE DST_DATES
PRINT DATE
BY DATE NOPRINT
RUN
FILE DST_AGENTS
PRINT AGENT_ID
BY AGENT_ID NOPRINT
AFTER MATCH HOLD NEW
END
-RUN
TABLE FILE HOLD
PRINT *
END
-EXIT

MATCH has some rather specific requirements in order for it to work. Waz and I explained some of the caveats in the post "Best ways to use MATCH (FOCUS) to Merge (one to many)". It may be worth looking at.

There may be a simpler way to approach this - but here is an idea:

  
-*LIST OF DST EMPLOYEES(AGENTS)
TABLE FILE SCHEDULE1
SUM COMPUTE BLANK/A1 = ' ' ;
BY AGENT_ID
ON TABLE HOLD AS DST_AGENTS FORMAT FOCUS INDEX BLANK
END
-RUN

-*LIST OF DST DATES
TABLE FILE SCHEDULE1
SUM COMPUTE BLANK/A1 = ' ' ;
BY DATE
ON TABLE HOLD AS DST_DATES FORMAT FOCUS  
END
-RUN

-* Create a combination of all employees against all days
JOIN CLEAR *
JOIN BLANK IN DST_DATES TO ALL BLANK IN DST_AGENTS AS J1

DEFINE FILE SCHEDULE1
EMP_CNT/I9 = 1 ;
END

-*MATCH STATEMENT THE 'ALL' FILE TO YOUR SCHEDULE
MATCH FILE DST_DATES
SUM BLANK
BY DATE  
BY AGENT_ID
RUN
FILE SCHEDULE1
SUM EMP_CNT
BY DATE  
BY AGENT_ID
-* If OLD-OR-NEW's result looks good, then OLD-NOT-NEW will give you only Employees that weren't against a date
AFTER MATCH HOLD OLD-OR-NEW
END
-RUN
TABLE FILE HOLD
PRINT *
END

To concatenate two tables with the same structure you can also use MORE instead of run.

  
MATCH FILE XXX
PRINT
   FIELD1
   FIELD2
BY FIELD3
ON TABLE HOLD AS YYY
MORE
FILE ZZZ
END
-RUN
TABLE FILE ZZZ
PRINT *
END

Thanks so much. This worked exactly like I wanted it to.

I'll go read the "Best ways to use MATCH (FOCUS) to Merge (one to many)"

That ain't gonna work.

MATCH FILE requires the initial BY field to be the same so you can't have BY DATE in one phase and BY AGENT_ID in the next.

You have two HOLD files without any common elements, so there is no way to make any connection between them.
Also, I don't see much point in using NOPRINT in a MATCH FILE statement. The following would work IF you had a date in the DST_AGENTS file.

MATCH FILE DST_DATES
BY DATE
RUN
FILE DST_AGENTS
PRINT AGENT_ID
BY DATE
BY AGENT_ID
AFTER MATCH HOLD   (you want OLD-AND-NEW and that's the default, so you can leave it out)
END

Your real problem is to display information that is NOT there - i.e. employee bbbbbb will NOT work on date 2.

You say your schedule looks like this:

DATE EMP
1 aaaaaa
1 bbbbbb
1 cccccc
2 aaaaaa
2 cccccc

So you have a list of all employees already within the SCHEDULE1 file A simple PRINT EMP by DATE will give the ones who WILL be working.

For employees not working I think something like this should work

-*LIST OF DST EMPLOYEES(AGENTS)
TABLE FILE SCHEDULE1
SUM AGENT_ID NOPRINT
BY AGENT_ID
ON TABLE HOLD AS DST_AGENTS
END

SET ALL=ON

JOIN AGENT_ID IN DST_AGENTS TO MULTIPLE AGENT_ID IN SCHEDULE1 AS J0

TABLE FILE DST_AGENTS
SUM CNT.SCHEDULE1.SEGNAME.AGENT_ID BY SCHEDULE1.SEGNAME.DATE BY SCHEDULE1.SEGNAME.AGENT_ID
WHERE DST_AGENTS.DST_AGENTS.AGENT_ID NE SCHEDULE1.SEGNAME.AGENT_ID
END

Twanette's approach is cool. Love it.

This message has been edited. Last edited by: George Patton,

Thanks George! You made my day

Ken,

Please post your solution, so we can all see what you came up with.

WOW, good discussion..

George, I've tried your code, and it didn't work. It generated no lines or data. Twanette's code does works. Here is what I have:

-DEFAULT &ECHO = ALL
SET ALL=ON
-* Get today's date
-SET &NOWDATE = TODAY('A10');
-* Rearrange the date so it can be used with TV
-SET &YYYYMMDD = EDIT(&NOWDATE,'$$$$$$9999') |'/'|
- EDIT(&NOWDATE,'999') |
- EDIT(&NOWDATE,'$$$99');
-SET &YYMD1 = EDIT(&YYYYMMDD);
TABLE FILE tv_except_data
PRINT *
ON TABLE HOLD AS TV_EXC_CODES FORMAT FOCUS INDEX EXC_ID
END
-RUN
-*-EXIT
TABLE FILE TV_AGENTS
PRINT
AGENT_ID
WHERE EFFECTIVE_DATE EQ '1/1/1901'
ON TABLE HOLD AS ALL_AGENTS FORMAT FOCUS INDEX AGENT_ID
END
-RUN
-*EXIT
JOIN
LEFT_OUTER TV_SCHEDULE_DATA.EXCEPTION_CODE IN TV_SCHEDULE_DATA TO UNIQUE
TV_EXC_CODES.EXC_ID IN TV_EXC_CODES AS J1
END
JOIN
LEFT_OUTER TV_SCHEDULE_DATA.AGENT_ID IN TV_SCHEDULE_DATA TO UNIQUE
ALL_AGENTS.AGENT_ID IN ALL_AGENTS AS J2
END
TABLE FILE TV_SCHEDULE_DATA
PRINT
AGENT_ID
AGENT_NAME
MU
SCH_START_MINUTE
SCHEDULE_LENGTH AS 'SCH_LENTH_MIN'
EXCEPTION_CODE
EXC_NAME
EXC_START_MINUTE
EXC_LENGTH AS 'EXC_LENGTH_MIN'
EXC_IN_OFFICE
BY DATE
BY AGENT_NAME
WHERE ( TV_SCHEDULE_DATA.MU EQ 830 )
AND ( DATE GE '&NOWDATE' );
ON TABLE HOLD AS SCHEDULE1 FORMAT FOCUS INDEX AGENT_ID DATE
END
-RUN
-*-EXIT
-*******Twanette post***
LIST OF DST EMPLOYEES(AGENTS)
TABLE FILE SCHEDULE1
SUM COMPUTE BLANK/A1 = ' ' ;
BY AGENT_ID
BY AGENT_NAME
ON TABLE HOLD AS DST_AGENTS FORMAT FOCUS INDEX BLANK
END
-RUN
-*-EXIT
-*LIST OF DST DATES
TABLE FILE SCHEDULE1
SUM COMPUTE BLANK/A1 = ' ' ;
BY DATE
ON TABLE HOLD AS DST_DATES FORMAT FOCUS
END
-RUN
-*-EXIT
-* Create a combination of all employees against all days
JOIN CLEAR *
JOIN BLANK IN DST_DATES TO ALL BLANK IN DST_AGENTS AS J1
DEFINE FILE SCHEDULE1
EMP_CNT/I9 = 1 ;
END
-*MATCH STATEMENT THE 'ALL' FILE TO YOUR SCHEDULE
MATCH FILE DST_DATES
SUM BLANK
BY DATE
BY AGENT_ID
BY AGENT_NAME
RUN
FILE SCHEDULE1
SUM EMP_CNT
BY DATE
BY AGENT_ID
BY AGENT_NAME
-* If OLD-OR-NEW's result looks good, then OLD-NOT-NEW will give you only Employees that weren't against a date
-*AFTER MATCH HOLD OLD-OR-NEW
AFTER MATCH HOLD OLD-NOT-NEW
END
-RUN
TABLE FILE HOLD
SUM
COMPUTE SCH_START_MIN/I1 = 0 ;
COMPUTE SCH_LENTH_MIN/I1 = 0 ;
COMPUTE EXC_START_MIN/I1 = 0 ;
COMPUTE EXC_LENGTH_MIN/I1 = 0 ;
COMPUTE EXCEPTION_CODE/I1 = 0 ;
COMPUTE EXC_NAME/A20 = 'NS DAY' ;
COMPUTE EXC_IN_OFFICE/A1 = 'O' ;
BY DATE
BY AGENT_ID
BY AGENT_NAME
-*ON TABLE HOLD AS NS_DAYS FORMAT FOCUS INDEX AGENT_ID DATE
END
-RUN
-EXIT
-*****George Patton post****
-*LIST OF DST EMPLOYEES(AGENTS)
-*TABLE FILE SCHEDULE1
-*SUM AGENT_ID NOPRINT
-*BY AGENT_ID
-*ON TABLE HOLD AS DST_AGENTS
-*END
-*-RUN
-*-*-EXIT
-*JOIN AGENT_ID IN DST_AGENTS TO MULTIPLE AGENT_ID IN SCHEDULE1 AS J3
-*TABLE FILE DST_AGENTS
-*SUM CNT.SCHEDULE1.SEG01.AGENT_ID BY SCHEDULE1.SEG01.DATE BY SCHEDULE1.SEG01.AGENT_ID
-*WHERE DST_AGENTS.AGENT_ID NE SCHEDULE1.SEG01.AGENT_ID
-*END
-*-RUN
-*-EXIT

If I have time today (have to take a sick dog to the vet) I'll try to refine my method to see if I can get it to work. Seems to me it should be possible with the ability to specify FILENAME.SEGNAME.FIELDNAME

I tried your suggestion George but not able to get any results with my data set.

I should have mentioned that my data has multiple rows for each agent_id by day (usally 3). I don't know if that is the reason I get no results.

I changed the PIN to AGENT_ID and HIREDATE to DATE to match my fields:
TABLE FILE SCHEDULE1
PRINT AGENT_ID AGENT_NAME DATE
BY AGENT_ID NOPRINT
-*WHERE HIREDATE GE 19900101
ON TABLE HOLD AS FULLSET
END
-RUN
-*Get the subset (aka the schedule)
TABLE FILE SCHEDULE1
PRINT AGENT_ID AGENT_NAME DATE
BY AGENT_ID NOPRINT
-*WHERE HIREDATE GE 19910101
ON TABLE HOLD AS SUBSET
END
-RUN
-*Join the subset to the full set
JOIN CLEAR *
JOIN FULLSET.FULLSET.AGENT_ID IN FULLSET TO SUBSET.SUBSET.AGENT_ID IN SUBSET AS J0
END
-*Table counts the ones that are in the full set but not in the subset by date.
TABLE FILE FULLSET
COUNT FULLSET.FULLSET.AGENT_ID BY FULLSET.FULLSET.DATE
WHERE FULLSET.FULLSET.AGENT_ID NE SUBSET.SUBSET.AGENT_ID
END
-EXIT

Hallooo?

The first TABLE ... HOLD and the second are identical, so why would you expect the final request to find any instances satisfying
"WHERE FULLSET.FULLSET.AGENT_ID NE SUBSET.SUBSET.AGENT_ID"
?

There is nothing in your approach that would cause every possible DATE to be considered for each AGENT_ID, so it is domed to disappoint.

The way to go is what I suggested, as fleshed out by Twanette.

I have used Twanettes suggestion and it is working perfectly. However, in my email notification I got an alert that George posted a suggestion which for some reason isn't on here? I still have the email so I know I'm not crazy or anything (at least I don't think lol) Here is what I got from George:

George Patton
posted June 27, 2013 02:29 PM
OK - This works, using IBI sample data:

1) Get a list of all employees

TABLE FILE EMPDATA
PRINT PIN HIREDATE
BY PIN NOPRINT
WHERE HIREDATE GE 19900101
ON TABLE HOLD AS FULLSET
END


Get the subset (aka the schedule)

TABLE FILE EMPDATA
PRINT PIN HIREDATE
BY PIN NOPRINT
WHERE HIREDATE GE 19910101
ON TABLE HOLD AS SUBSET
END


Join the subset to the full set

JOIN CLEAR *
JOIN FULLSET.FULLSET.PIN IN FULLSET TO SUBSET.SUBSET.PIN IN SUBSET AS J0
END


Table counts the ones that are in the full set but not in the subset by date.

TABLE FILE FULLSET
COUNT FULLSET.FULLSET.PIN BY FULLSET.FULLSET.HIREDATE
WHERE FULLSET.FULLSET.PIN NE SUBSET.SUBSET.PIN
END


WebFOCUS 7.7.04
Windows, Linux, All Outputs

Because I'm still learning and wanted to try his suggestion to see if would work or not. The end result is that his suggestion works with the EMPDATA tables but I not with mine.

Thanks for everyone's input on this. This forum has been a life saver for me and I don't think I would have accomblished anything without it.