When a percentage option is created (such as for a tax or fee), the program Edit Percent Matrix is used to define which of the other possible charges should be subject to the tax or fee. When there are changes in the items subject to the tax or fee, another Percent Matrix record must be created. For operations with many locations, it can be rather time consuming to do the necessary data entry in that program since each record must be created and/or adjusted manually. Therefore, this import program is available as an alternative and can be used to create and/or update records in the Percent Matrix. Please refer to the chapter Edit Percentage Matrix for a full discussion of how the percentage matrix functions.

Use this program to:
Each record in the Percent matrix is referred to as a 'table'. The combination of the first 4 fields from that program (and the corresponding first 4 fields of the import file) are what define the table. Those fields are:
If the data in these 4 fields matches an existing Percent Matrix table, the table is updated.
If the data in any of those fields is different from an existing Percent Matrix table, it results in a new table being created (rather than updating an existing table).


I. CREATING THE SPREADSHEET

A. Create the spreadsheet on a PC or computer that is also used to access CARS+.


B. Entering data in the spreadsheet

  • The columns for the spreadsheet should not have headings.
  • The data entered in each row must be entered in the order shown below.

Column # Data to be entered
A Location code
B Option Code of the percent-based option
C Effective date (see User Tip below.)
D Priority (may be left blank if priority does not apply)
E Option Code that the tax or fee in Column B will be applied to
F Continue entering each Option Code that the tax or fee should be applied to. One option code per column.

Another way to think of it is that each row in the spreadsheet becomes a record in the Percent Matrix file. Each column of the row represents a field in that record. See Examples at the end of this chapter.

User tip: Date format can be either:

YYYYMMDD (Example: 20190516 for May 19, 2019)

The local date format of either: MM-DD-YY or DD-MM-YY (This is specified in Edit Misc Control Record.)

C. Naming the spreadsheet file.

When saving the spreadsheet, the name assigned to it should follow Unix file name conventions which are:

  • Maximum of 30 characters
  • No spaces or slashes in the name

D. Saving the spreadsheet.

First save it in your spreadsheet program's native format (for example if using Microsoft's Excel program, save it as .xls or.xlsx).

Re-save a copy in .csv (Comma Separated Values) format.

If an error in the data is discovered by the merge program, go back to the file in the native format. It should be easy to make any corrections. Re-save it in the native format. Then re-save it again as a comma delimited file (.csv format).

It is the copy saved in .csv format that will be used for the import.


II. UPLOADING THE FILE FROM THE PC TO CARS+

Create the spreadsheet on a computer that is also used to access CARS+. Once the spreadsheet is saved in the csv format, sign into CARS+ using the same computer.

To access the Import Percent Matrix program, type IMPMAT (RET) at any menu "OPTION:" field or the appropriate line number on the Import Data sub-menu of the System Management menu.

III. RUNNING THE IMPORT PROGRAM


To access the program, type IMPMAT (RET) at any menu "OPTION:" field or the appropriate number on the Data Import Menu. The screen will then display:

OPTION: IMPORT PERCENT MATRIX


1 Filename
2 Clone from Previous Y

NOTE: DATE FORMATS HAVE CHANGED. ACCEPTABLE FORMATS ARE:
MM-DD-YY
YYYYMMDD




1DO IMPORT2 3ERASE 4UPLOAD 5BACKUP 6 7HELP 8PRINT


Enter data as follows:

1. FILE NAME

The file can be loaded for processing by either:

a. typing the file name in this field
-or-
b. searching for it. To search, press F6 from this field. A search window will appear on the lower half of the screen. Select the appropriate file by typing the row number. An example is shown below.
2. CLONE FROM PREVIOUS
 This field determines if the imported file is an update to an existing matrix table or if a new table should be created (from scratch). Enter:

Y = For each row in the import file, the program will first copy the settings to the new effective date from the existing table that is currently in effect. It will then treat the items in columns E and onward as updates to those settings. Therefore, only the changes to the current table need to be listed in Columns E and the following columns. (See Example 1 at the end of this chapter.)
To remove an option from an existing record, precede it with a minus sign. (See Example 2 at the end of this chapter.)
N = The new table will be based entirely on the options listed in the import file, columns E onward. Therefore, every option that is to be set to "Y" needs to be listed. In this mode, there is no point to prefixing an option with a minus sign as there is nothing to delete. This method creates an entirely new table from scratch. (See Example 3 at the end of this chapter.)

OPTION: __ IMPORT PERCENT MATRIX

1. From file named







Option: _ ADJUSTMENTS FILE SEARCH
F2=NEXT 8 F4=PREV 8
File Name Create Date
1 matrix1-050219.csv May 02 2019
2 matrix2-050219.csv May 02 2019
** No More Records **





Select the appropriate file by typing the row number.

PRESS "F1" AT "OPTION:" TO BEGIN .


Examples of spreadsheets


Example 1:
At location SNA, as of July 1, 2019, options LDW and PAI will start being subject to the TAX calculation. Additionally, LDW will also be subject to the airport fee (APTAX).

A B C D E F G H
SNA TAX 20190701
LDW PAI

SNA APTAX 20190701
LDW




Example 2:
At location SNA, as of July 1, 2019, the FUEL charge will no longer be subject to the TAX calculation. (note minus sign preceding option code "FUEL".)
A B C D E F G H
SNA TAX 20190601
-FUEL












Example 3:
A new county tax (CNTY) is going into effect July 1, 2019 at location SNA. A new Matrix table is being created showing the options subject to the tax.

A B C D E F G H
SNA CNTY 20190701
TIME MILES DRIVR AGE25