Example - Using @TEXT for Staff Scheduling

Let's once again make use of the staff scheduling model from Using Sets. However, this time we will modify it to use the @TEXT function and write the solution to a file. The model follows with the critical change listed in bold type:

SETS:

  DAYS / MON TUE WED THU FRI SAT SUN/:

   REQUIRED, START;

ENDSETS

 

DATA:

  REQUIRED = 20 16 13 16 19 14 12;

  @TEXT( 'OUT.TXT') = DAYS, START;

ENDDATA

 

MIN = @SUM( DAYS( I): START( I));

 

@FOR( DAYS( J):

  @SUM( DAYS( I) | I #LE# 5:

     START( @WRAP( J - I + 1, 7)))

        >= REQUIRED( J)

);

We have added the one output operation:

  @TEXT( 'OUT.TXT') = DAYS, START;

which writes the values of the DAYS set and the values of the START attribute to the file OUT.TXT. Once you solve the model, LINGO will run this output operation, the file OUT.TXT will be generated and it will contain the members of the DAYS set and the optimal values for the START attribute:

MON    8.0000000

TUE    2.0000000

WED    0.0000000

THU    6.0000000

FRI    3.0000000

SAT    3.0000000

SUN    0.0000000

File: OUT.TXT

You may now import the data from OUT.TXT into other applications. For instance, if you want to import the data into MS Access, you could use the File|Get External Data|Import command in Access to read the data into a table. We defined a small table in Access called Start and imported the data in this manner yielding the following:

page248axp

To import the data into an Excel sheet, you must first use the File|Open command on the OUT.TXT file to get the data into a spreadsheet by itself, as we have done here:

page248bxp

Once the results are imported into a spreadsheet, you may cut and paste them to any other sheet you desire.

Now, suppose you are not interested in all the output generated by the standard LINGO solution report. Suppose, in the case of this example, all you want to see is the objective value and the values for the DAYS set and the START attribute. Here's how you can do it. First, add the additional output operation, shown here in bold, to your data section, so it looks like:

DATA:

  @TEXT( ) = DAYS, START;

ENDDATA

The new output operation causes the values of DAYS and START to be sent to the screen (since we omitted a file name). Next, you will need to suppress the normal LINGO solution report. In Windows versions of LINGO, select the Solver|Options command, click on the Interface tab in the options dialog box, check the Terse output checkbox, then press the OK button (on platforms other than Windows, enter the TERSE command). Now, solve your model and you will be presented with the following, abbreviated report:

Global optimal solution found at step:         8

Objective value:                        22.00000

 

MON    8.0000000

TUE    2.0000000

WED    0.0000000

THU    6.0000000

FRI    3.0000000

SAT    3.0000000

SUN    0.0000000

In the example above, we simply listed the names of set DAYS and attribute START on the right-hand side of our output operation.  This causes LINGO to display all values for DAYS and START.  Suppose we'd like more control over what values do and do not get displayed.  In particular, suppose we are only interested in viewing those days in which the value for START is nonzero.  We can do this by using the @WRITEFOR report function, which allows us to provide a condition to test before printing output:

DATA:

  @TEXT( ) = @WRITEFOR( DAYS( D) | START( D) #GT# 0:

   DAYS( D), @FORMAT( START( D), '6.1f'));

ENDDATA

Note how we now only display the days where START > 0 in our new report:

Global optimal solution found at iteration:            15

Objective value:                                 22.00000

 

MON   8.0

TUE   2.0

THU   6.0

FRI   3.0

SAT   3.0

Another feature of this last example to note is the use of the @FORMAT function, which we used to display the nonzero start values in a field of six columns with one trailing decimal point.

@WRITEFOR also allows us to form arithmetic expressions of the variable values.  Here's an example that we could add to our staff model to compute the number of staff on duty each day:

DATA:

  @TEXT( ) = @WRITE( 'Day    On-Duty');

  @TEXT( ) = @WRITE( 14*'-');

  @TEXT( ) = @WRITEFOR( DAYS( D): DAYS(D),

   @FORMAT( @SUM( DAYS( D2) | D2 #LE# 5:

    START( @WRAP( D - D2 + 1, 7))), '11.1f'));

ENDDATA

Here's the report generated by these output operations:

Day    On-Duty

--------------

MON       20.0

TUE       16.0

WED       13.0

THU       16.0

FRI       19.0

SAT       14.0

SUN       12.0

This previous example also illustrates the use of the @WRITE function.  The @WRITE function is similar to the @WRITEFOR function with the exception that it does not accept a set to loop on, and, therefore, is used to write single occurrences of text output.  As with @WRITEFOR, @WRITE accepts expressions of variables.  Here's an example that calculates the maximum number of employees starting on a particular day.

DATA:

  @TEXT() = @WRITE( 'Max start = ', @MAX( DAYS: START));

ENDDATA

which yields the following output: Max start = 8.