I am very new to WF(been doing this for ~3 weeks).

I need a report (Excel) listing all active employee-id numbers and levels for every week between 1/1/2010 and 4/24/2015 (The week runs Saturday - Friday).

I have been digging through the forums and manuals and have come up with the following, any advice or direction would be appreciated. This test file covers 01/2015 - 4/24/15, I am also trying print one week of data per tab (BYTOC).



-DEFAULT begindate = '01/01/2015';
-DEFAULT enddate = '04/24/2015';


-* calculate number of weeks between the begin date & end date
-SET &beginfmt = DATECVT(EDIT(&begindate,'99$99$9999'),'A8MDYY','I8');
-SET &endfmt = DATECVT(EDIT(&enddate,'99$99$9999'),'A8MDYY','I8');
-SET &total_weeks = (DATEDIF(&beginfmt, &endfmt, 'D'))/7;
-SET &this_week = 0;
-SET WEEKFIRST = 7


-REPEAT ACTIVELOOP FOR &this_month FROM 0 TO &total_weeks;

-SET &holdfile = 'WEEK'|&this_week;

DEFINE FILE JOB_RPT
THISWEEK/MDYY = (DATEADD(&beginfmt, 'D', &this_week))/7;
EFFECTIVEDATE = EFFDT

END

TABLE FILE JOB_RPT

PRINT
JOB_RPT.EMPLIDNMBR
JOB_RPT.LVL


BY
THISWEEK

WHERE
EMPL_STATUS IN ('ACTIVE','LEAVE');


ON TABLE HOLD AS &holdfile

END


-ACTIVELOOP

-SET &this_week = 1;

MATCH FILE WEEK0
BY THISWEEK

-REPEAT MATCHLOOP FOR &this_week FROM 1 TO &total_weeks;

RUN

-SET &holdfile = 'WEEK'|&this_week;

FILE &holdfile
BY THISWEEK

-MATCHLOOP

END
-RUN

ON TABLE PCHOLD FORMAT EXL2K BYTOC

END

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

CSun, what is your exact question?

Hi CSun and welcome to Focal Point.

Just a couple of points that I would raise with some of your code in the interests of encouraging good coding practice and potentially less pain in an upgrade.

Firstly, when referencing ampervariables always code the & or && (local or global) as this is unlikely to parse correctly in WF8.

Likewise, all defined fields should be terminated by a semicolon - even the final one! This may parse in 7.7.03 but it is incorrect syntax and is not guaranteed to work in future releases!

Be aware of the differences between -SET and SET. the line that you have or -SET WEEKFIRST = 7 is an environmental setting and should, therefore be SET not -SET. If I have misinterpreted this and you are using WEEKFIRST as an ampervariable then I would encourage you not to use reserved words for labels, fieldnames, ampervariables etc.

In your code you are processing the source many times (about 17 times for the dates that you have as defaults) and this could make your process very inefficient depending upon the RDBMS and the number of records that will satisfy the predicate that you have. You would be better served to calculate the THISWEEK value depending upon the relevent date field within your data source.

Some of these points would be covered in basic training so if you haven't had any then I would urge you to request some via your manager(s) as the benefit would be to both you in doing this task that is asked of you but also, by giving you the knowledge to be more proficient, your company will gain in the potential increase in workflow!

I know this hasn't answered your question but I think that there are fundamental knowledge opportunities that are required first IMHO.

Having said all that, the approach I would take is -

• Identify the field that you need to use to identify a week number and use that within a define
• Use a single pass of the data using a predicate on the date field from &begindate to &enddate as well as the one on empl_status
• Sort on the weeknumber defined field using BYTOC (as you have currently)

Hopefully that should help you forward.

T