Difference between revisions of "Member Attributes"

From Vital Soft Wiki
Jump to: navigation, search
>Johno
(Attributes from SHXREFCCYYMMDD.xls or LNXREFCCYYMMDD.xls)
>Johno
(Calculating Attributes from SHXREF)
Line 182: Line 182:
 
==== Calculating Attributes from SHXREF ====
 
==== Calculating Attributes from SHXREF ====
  
Attributes 11-15 are calculated using the SHXREF file.  When an attribute is based on a member having specific share products, the SHXREF file is the easiest way to configure the corresponding attribute (and product).
+
Attributes 11-15 are calculated using the %VS-DAYS-LOAN-DELQ.  When an attribute is based on a member having specific share products, the SHXREF file is the easiest way to configure the corresponding attribute (and product).
  
 
For the above example, the SH_ATTR_NBR column of the SHXREF file is assigned the value 11 (Savings Account) for all rows where the share DESC-ABRV is a savings product.  Since attribute 11 is also product 1, the SH_PROD_NBR will be assigned a 1.  All Checking Account DESC-ABRVs will be assigned SH_ATTR_NBR=12 and SH_PROD-NBR=2.
 
For the above example, the SH_ATTR_NBR column of the SHXREF file is assigned the value 11 (Savings Account) for all rows where the share DESC-ABRV is a savings product.  Since attribute 11 is also product 1, the SH_PROD_NBR will be assigned a 1.  All Checking Account DESC-ABRVs will be assigned SH_ATTR_NBR=12 and SH_PROD-NBR=2.

Revision as of 18:26, 1 December 2015

Overview

Member attributes are used in the Membership_Analysis and Membership_Summary documents. Member attributes allow a quick selection of members who have (or don't have) a particular attribute.

Typical Attributes are:

  • Single Service
  • OK to Send Mail
  • Specific Services, like:
  • Online Banking
  • eStatements
  • Direct Deposit
  • RDC
  • Debit Card
  • Specific share products, like:
  • Savings
  • Checking
  • CD
  • IRA
  • Specific loan products, like:
  • LOC
  • Vehicle
  • Mortgage
  • Other:
  • Charged Off Share
  • Charged Off Loan
  • Delinquent Loan

Attributes can be determined in a variety of ways:

  • Calculated during the nightly core extract. Many attributes are calculated from member level flags, but almost any custom logic may be used.
  • Based on specific share products by editing the SHXREF file.
  • Based on specific loan products by editing the LNXREF file.

The definition of an attribute can combine multiple flags. For example, the OK to Send Mail attribute could check a bad address flag, a deceased flag and an opt-out flag. If any of these flags are set, the attribute would be false. At most 30 attributes can be defined.

Sample Member Attributes

Member Attributes.png

Host ATTRFILE

The host ATTRFILE is used to define the attribute names, numbers and their calculation logic. The file is saved in the custom/live directory in /ASKPLUS/vsdata.

This file is a text file, that must have a very precise layout.

Name (Note) Columns Required Purpose
NBR 1-5 Yes The attribute name.
IDX 6-10 Yes (*) The attribute variable index if this attribute is calculated during the core extract.
CNT 11-15 No The product number if this attribute is to be counted like a product.
PRIMARY.TOPIC 16-45 Yes The primary topic if the attribute is calculated during the core extract. Else N/A.
INCLUDE 46-55 Yes (*) Name of an AskPlus include file if this attribute is calculated during the core extract.
FIELD-NAME 56-75 Yes The name of the attribute field in the loaded document.
Attribute Description 76-105 Yes The displayed name of the attribute in the loaded document.
X 106 Yes A single X character that marks the end of each record.
* Required only if the attribute is being calculated during the host extract.
NBR:
Must be a number between 1 and 30. The number determines the position of the attribute when it is displayed in the document. Attribute numbers do not have to be contiguous.
IDX:
Is the index of the #ATTR variable that calculates the attribute. Refers to the calculation in the AskPlus include file in the INCLUDE column. The index must be between 1 and 30, but does not need to be contiguous. See examples below.
CNT:
Is the corresponding product number for this attribute. Products are counted and selections can be made based on the product count. To be counted, products must be defined as attributes.
PRIMARY.TOPIC:
Is the name of the primary topic if the attribute is being calculated during the host extract. For example, MEMBRS.MEMBER-FILE. This column must be set to N/A if the attribute is calculated later using SHXREF or LNXREF.
INCLUDE:
The name of a host file located in the same directory as the ATTRFILE. This file must be a valid AskPlus include file that calculates the attribute as #ATTR(IDX), where the IDX is specified in the IDX column.
FIELD-NAME:
Is the name of the field that will be loaded in the Qlik documents. This name will be shown in the Current Selections window and used when making a Document Distribution selection.
Attribute Description:
The attribute description that will be displayed in the Qlik document. See screen shot below.
The X Column:
The last character on each record must be X. This column is used to help ensure that each record has exactly 106 characters. Trailing spaces are not allowed.

Transferring the file to UNIX:

If you edit the file locally and then transfer it to UNIX, make sure the file only contains linefeed characters and does not contain and carriage returns (^M). Using an ASCII file transfer is the easiest way to eliminate unwanted carriage returms. The dos2ux utility will also work. Viewing the file in vi is the easiest way to check for unwanted carriage returns as they will be displayed as ^M.

Sample ATTRFILE

The example below shows a partial ATTRFILE:

NBR__IDX__CNT__PRIMARY.TOPIC_________________INCLUDE___FIELD-NAME__________Attribute Description_________X
1    1         SHLNTOTS.SHLNTOTS             ITOTATTR  SINGLE_SERVICE      Single Service                X
2    1         MEMBRS.MEMBER-FILE            IMBRATTR  OK_TO_MAIL          OK to Mail                    X
3    3         MEMBRS.MEMBER-FILE            IMBRATTR  DORMANT             Dormant                       X
4    4         MEMBRS.MEMBER-FILE            IMBRATTR  INACTIVE            Inactive                      X
6    5         MEMBRS.MEMBER-FILE            IMBRATTR  ONLINE_BANKING      Online Banking                X
7    6         MEMBRS.MEMBER-FILE            IMBRATTR  E_STATEMENTS        eStatements                   X
8    7         MEMBRS.MEMBER-FILE            IMBRATTR  DIRECT_DEPOSIT      Direct Deposit                X
11        1    N/A                                     SAVINGS             Savings Accounts              X
12        2    N/A                                     CLUB_ACCTS          Club Accounts                 X
13        3    N/A                                     CHECKING_ACCTS      Checking Accounts             X
14        5    N/A                                     SHARE_CERTS         Share Certificates            X
15        6    N/A                                     IRA_SAVING_CDS      IRA Savings/CDs               X
16        11   N/A                                     NEW_VEHICLE         New Vehicle                   X
17        12   N/A                                     USED_VEHICLE        Used Vehicle                  X
18        13   N/A                                     LOC                 LOC                           X
19        14   N/A                                     FIRST_MORTGAGE      1st Mortgage                  X
20        15   N/A                                     OTHER_REAL_ESTATE   Other Real Estate             X
21   1         MEMBRS.LOAN-FILE              ILNATTR   DELQ_LOANS          Delinquent Loans              X

Calculating Attributes from an SD file

The first attribute is calculated from an SD file that is created during the load called SHLNTOTS which counts the total number of active (open) shares and loans for each member. The ITOTATTR file contains the following:

* Calculate MEMBER-FILE Attributes from SHLNTOTS:
*
<< Single Service >>
#ATTR(1)=(%VS-ACTIVE-MBR AND SHLNTOTS.MBR-ACTIVE-SH-CNT = 1 AND SHLNTOTS.MBR-ACTIVE-LN-CNT = 0)
  • The macro VS-ACTIVE-MBR is configured in the main Vital Signs macro file.

Calculating Attributes from MEMBER-FILE

Attributes 2-4 and 6-8 are calculated from MEMBER-FILE. Notice how the IDX values in the sample ATTRFILE match the #ATTR(idx) in the IMBRATTR file. The IMBRATTR file contains the following.

* Calculate MEMBER-FILE Attributes:

<< OK To Mail >>
#ATTR(1)=(MEMBER-FILE.CONTROL-FLAGS(21) <> 1 AND MEMBER-FILE.CONTROL-FLAGS(24) <> 1 AND
          MEMBER-FILE.CONTROL-FLAGS(29) <> 1 AND MEMBER-FILE.CONTROL-FLAGS(52) <> 1 AND
          MEMBER-FILE.CONTROL-FLAGS(53) <> 1 AND MEMBER-FILE.CONTROL-FLAGS(57) <> 1)

<< Dormant >>
#ATTR(3)=(MEMBER-FILE.REST-FLAG-11=79)

<< Inactive >>
#ATTR(4)=(MEMBER-FILE.REST-FLAG-11=90)

<< Online Banking >>
#ATTR(5)=(MEMBER-FILE.CONTROL-FLAGS(67)=1)

<< eStatements >>
#ATTR(6)=(MEMBER-FILE.CONTROL-FLAGS(68)=1)

<< Direct Deposit >>
#ATTR(7)=(MEMBER-FILE.CONTROL-FLAGS(59)=1)

Calculating Attributes from SHXREF

Attributes 11-15 are calculated using the %VS-DAYS-LOAN-DELQ. When an attribute is based on a member having specific share products, the SHXREF file is the easiest way to configure the corresponding attribute (and product).

For the above example, the SH_ATTR_NBR column of the SHXREF file is assigned the value 11 (Savings Account) for all rows where the share DESC-ABRV is a savings product. Since attribute 11 is also product 1, the SH_PROD_NBR will be assigned a 1. All Checking Account DESC-ABRVs will be assigned SH_ATTR_NBR=12 and SH_PROD-NBR=2.

Calculating Attributes from LNXREF

Attributes 16-20 are calculated using the LNXREF file. When an attribute is based on a member having specific loan products, the LNXREF file is the easiest way to configure the corresponding attribute (and product).

For the above example, the LN_ATTR_NBR column of the LNXREF file is assigned the value 16 (New Vehicle) for all rows where the share COLLATERAL code qualifies as a new vehicle loan. Since attribute 16 is also product 11, the LN_PROD_NBR will be given the value 11. All Used Vehicle COLLATERAL codes will be assigned LN_ATTR_NBR=17 and LN_PROD-NBR=12.

Calculating Attributes from LOAN-FILE

Attribute 21 (Delinquent Loan) is calculated using LOAN-FILE. The LNXREF cannot be used to assign this attribute since it involves checking the loan balance and due-date. Setting up this attribute is very similar to the MEMBER-FILE attributes. In this case the primary topic is LOAN-FILE and the include file (ILNATTR) looks like this:

* Calculate LOAN-FILE Attributes:
*

NEWREG DAYS-DELQ:I2
#DAYS-DELQ = %CYMD-DIFF-DAYS(LOAN-FILE.DUE-DATE, #ASOFDATE)

<< Delinquent Loan >>
#ATTR(1)=(%VS-OPEN-LOAN AND LOAN-FILE.BALANCE > 0 AND #DAYS-DELQ >= %VS-DAYS-LOAN-DELQ)
  • The macros VS-OPEN-LOAN and %VS-DAYS-LOAN-DELQ are configured in the main Vital Signs macro file.