Parametric remap example
A Parametric remap is used to apply queries to the data in the database.
This can be done at the time of the load or at a later date. An external file containing the SQL query is made and then referenced when the Parametric Remap is loaded in iLoader. This creates a new domain which contains the results of the query (ie. Yes or No values). This domain can then be used in further data manipulation if required.
The following example displays an external file, created in Notepad, to create a parametric remap from the Household table.
The script is written in the following format:
PARAMETRIC, Full Table Name , Parametric Field Name , External File Name ,
NSQL Declarations, Value Column Number
If you are using the Data Transformation wizard, these entries relate to the column fields:
| PARAMETRIC | Data Transformation function |
| Full Table Name | Parent Table |
| Parametric Fieldname | Short name of bin |
| External File Name | Name of file containing parameters |
| NSQL Declaration | Column in file containing NSQL declarations |
| Value Column Number | Column in the file containing parameter labels |
The following example creates a new field called Household Age profile using the HouseholdAgeProfile.txt file from the Household table.
PARAMETRIC, [Demo].[Household], Household Age profile, HouseholdAgeProfile.txt, 1, 2
| Note: | The Column Number of the SQL Query refers to the column containing the SQL expression in the external text file and the Column Number of the Label refers to the column containing the label to be applied to the data. |
There is an inline version of the PARAMETRIC function called IPARAMETRIC. The format is as follows:
IPARAMETRIC table , new_field_name ,
{ select_statement | description select_statement | description }
The following example creates a column called Test_1 from the 'test' database.
IPARAMETRIC [test].[person] , Test_1 , {
Select # from [test].[person] where [test].[person].[GENDER] = "M";|Male
Select # from [test].[person] where [test].[person].[GENDER] = "F";|Female
Select # from [test].[person] where [test].[person].[GENDER] = "U";|Unknown
}
