ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
3
Version:4.00
4
5
Standards for completion:
6
7
Headers should be included in every file for the validation process to verify columns.
8
9
Every tab in the spreadsheet is a unique file.
10
11
Begin and End Dates in various templates allow for change tracking when names, addresses, etc. change. Failure to populate those dates will leave the table static.
12
13
14
15
Rules for Completing Data Templates
16
for Client Extensions see below
17
Cloverleaf
Template
Target		Column A - NameRepresents the data element found in the Cloverleaf tables.
18
Column B - Data Type
Identifies what type of data this is. This must be a valid Data Type from the Domain Datatypes spreadsheet.
19
Column C - RequiredIndicates whether a value for the data element is required, optional or recommended. Recommended tables are ones we see frequently used by clients and often used in reporting. For Required tables the Client must map either a Table/Column to be used or a Default Value.
20
Column D - Code Name
The Cloverleaf Code Table that is used to validate the Client code from the Client Table/Column (Columns G & H) or from the Default Value (Column K). All codes must be in the referenced table, including N/A if used as a Default Value. Note - Any codes in the table that are shown in red cannot be modified.
21
Column E - Comments
Further explanation of what is contained in the data.
22
Column F - NotesUsed for additional notes/requirements.
23
Client
Source		Column G - TableIf the value is to be found in one of the Client's source tables enter the name of that source table here.
24
Column H - ColumnIndicate the name of the column in that source table (from Column G above) that contains the information the client wants to see here. If Column C is Required the Client must map either a valid Table/Column or a Default Value. If Column C is No the Client has the option of mapping a valid Table/Column, Default Value, or showing N/A under Column C to indicate they have reviewed and choose to not map. All codes found in the Client Table/Column must be added to the CLA Code table.
25
Column I - Mapping Rule
Used to provide specific mapping rules to the ETL programmer. Examples would be to CONCAT multiple column values together, or to SUBSTRING to find the location of the desired value.
26
Column J - Client Notes
Used for additional notes/requirements specific to the Client.
27
Default
Value		Column K - LiteralThe client can indicate a Default Value for the data element. The default will be used if no Table/Column is specified or if no value is found in the specified Table/Column. If a Code Name is shown in Column D the Default Code MUST be in that code table. This includes any defaults to "N/A". "N/A" would need to be added as a valid code. The exception is a default of a 'date'. They do not get added to the code tables.
28
Column L - Set ToIf the default is to set it to the value found in another data element specify that data element here. Example - The Claim_Component_Reported_Date is mapped to come from TABLE123, Column RPTDTE. If the client puts "Claim_Component_Open_Date" in the Default Value Set To the system will set it to the value found in Claim_Component_Open_Date if the RPTDTE is not found or empty.
29
Quick Reference Guide to Requirements
30
If the Data Element is Required
Either the Client Source Table/Column must be completed or the Default Value must be completed.
31
Data Element Required
Column C		Code Name
Column D		Client Table/Source
Column G&H		Default Value**
Column K or L		RequirementExample from a client template
32
RequiredNOT BlankNOT BlankBlankMust validate that all codes in the client source table are included in the Code Spreadsheet code table. See Code Table tab for additional rules.Policy Transaction tab, Row 22 Amount_Type_Code. All of the clients codes must be in the Policy_Amount_Type_Code table.
33
RequiredNOT BlankBlankNOT Blank Must validate that the Default value listed is included in the Code Spreadsheet code table. This includes "N/A" (see line 20 below). Exception would be defaulted 'dates'. See Code Table tab for additional rules.Policy Transaction tab, Row 24 Amount_DCA_Code. The Default Value shown must be in the Amount_DCA_Code table.
34
RequiredNOT BlankBlankN/AIf the data element is required, and it references a code table, AND the client does not want to map anything into that data element, they can put N/A in the default. But, because a table is referenced, N/A must be added as valid code into that code table.The N/A would be set as a default value if the data element is required but the Client doesn't capture this information or have an alternative default to use.
35
RequiredBlankNOT BlankBlankNo table validation is performed. CLA will use whatever code, value is in the client file.Policy Transaction, Row 26 Policy_Amount.
36
RequiredBlankBlankNOT Blank No table validation is performed. CLA will move the default value.Policy Transaction, Row 12 Insurable_Object_Source_Identifier
37
38
39
If the Data Element is Not Required.
40
Data Element Required
Column C		Code Name
Column D		Client Table/Source
Column G&H		Default Value**
Column K or L		RequirementExample from a client template
41
Optional or RecommendedNOT BlankNOT BlankBlankMust validate that all codes in the client source table are included in the Code Spreadsheet code table listed.Policy tab, Row 23 Status_Code. All of the client codes must be in the Status_Code code table.
42
Optional or RecommendedNOT BlankBlankNOT BlankMust validate that the Default value listed is included in the Code Spreadsheet code table.Policy tab, Row 37, Bill_Method_code. Must validate that the default selected is included in the Bill_Method_Type_Code code table. Example - All of your business is 'Direct' billed.
43
Optional or RecommendedNOT BlankN/ABlankIf the data element is not required, and the client does not wish to map anything to it, we put N/A in Column H to indicate that we did not accidently miss this row.Policy tab, Row 26 Status_Reason_SubCode. Data element is not required and client does not want to include a subcode so we put N/A so indicate that we reviewed.
44
Optional or RecommendedBlankN/ABlankIf the data element is not required, and the client does not wish to map anything to it, we put N/A in Column H to indicate that we did not accidently miss this row.Policy tab, Row 29 Claims_Made_Or_Occurance. Data element is not required and client does not want to include a subcode so we put N/A so indicate that we reviewed.
45
46
47
48
49
When adding a client code to the Code tables you can just add your client information into Columns B, C & D on the row showing the corresponding CLA code. Or if no CLA code exists you can make up a new CLA code, or put your client code into both Column B & Column E. In other words, as long as the CLA code is not in red, you can put what you want in Column E .
50
51
**Default Value
52
Default Values are used when there is no table to retrieve the value from but the data element is required. They can be used to set defauts if the customer has only one option for that value. A good example would be 'Source System'. If the customer has only one source they can default it rather than retrieving from a table. They are also used to set a default value if nothing is found in the mapped Table/Column.
53
Literal - Put the Default Value under the Literal column if you want a specific code (including N/A) or date to be inserted here.
54
Set to - Put the name of another data element in the Set To column if you want this data element to be set to the value of another data element.
55
56
57
CLIENT EXTENSIONS
58
Attempt should be made to map the Client data to the existing Cloverleaf tables. If no entry exists for data the Client wants to capture extensions can be added to the tables.
Highlighted information is required.
59
60
Data Template Spreadsheet
61
Extension to Cloverleaf TemplatesColumn A - NameRepresents the name of the data element the client is adding. We suggest adding your client name as the Prefix.
62
Column B - Data Type
Identifies what type of data this is. This must be a valid Data Type from the Domain Datatypes spreadsheet.
63
Column C - RequiredIndicates whether a value for the data element is required, optional or recommended. Recommended tables are ones we see frequently used by clients and often used in reporting. For Required tables the Client must map either a Table/Column to be used or a Default Value.
64
65
66
67
Client
Source		Column G - TableIf the value is to be found in one of the Client's source tables enter the name of that source table here.
68
Column H - ColumnIndicate the name of the column in that source table (from Column G above) that contains the information the client wants to see here. If Column C is Required the Client must map either a valid Table/Column or a Default Value. If Column C is No the Client has the option of mapping a valid Table/Column, Default Value, or showing N/A under Column C to indicate they have reviewed and choose to not map. All codes found in the Client Table/Column must be added to the CLA Code table.
69
Column I - Mapping Rule
Used to provide specific mapping rules to the ETL programmer. Examples would be to CONCAT multiple column values together, or to SUBSTRING to find the location of the desired value.
70
Column J - Client Notes
Used for additional notes/requirements specific to the Client.
71
Default
Value		Column K - LiteralThe client can indicate a Default Value for the data element. The default will be used if no Table/Column is specified or if no value is found in the specified Table/Column. If a Code Name is shown in Column D the Default Code MUST be in that code table. This includes any defaults to "N/A". "N/A" would need to be added as a valid code. The exception is a default of a 'date'. They do not get added to the code tables.
72
Column L - Set ToIf the default is to set it to the value found in another data element specify that data element here. Example - The Claim_Component_Reported_Date is mapped to come from TABLE123, Column RPTDTE. If the client puts "Claim_Component_Open_Date" in the Default Value Set To the system will set it to the value found in Claim_Component_Open_Date if the RPTDTE is not found or empty.
73
74
Column AColumn BColumn CColumn GColumn HColumn IColumn JColumn KColumn LColumn KColumn L
75
Example
76
ext_client_new_datacla_generic_nameOptionalclient_tableclient_columnany specific rules for mappingAdd additional fields starting in the row below.  Select the ODS table to update from the list provided in the  ODS Table column.  All client added column names must begin with 'ext_' to properly identify extensions.  Example: 'ext_client_new_data'If value is to be defaulted enter informaiton hereIf the default is to be based upon another data element enter hereThis identifies the Cloverleaf table that the client specific information will be added to. The ODS table should be selected from the drop down. Usually this is set to TRUE as the client typically wants this data to show in the DW.
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100