# Standardizing customer birth dates with a dynamic schema Standardize customer birth dates from different source formats while preserving customer names. Use a dynamic schema to identify the birth date field at runtime. ## About this task A retailer receives customer records from several registration systems. Each system records birth dates in a different format. For example: ``` last_name,first_name,birthdate Martin,Claire,1985-04-23 Patel,Arjun,17/09/1992 Garcia,Elena,12-05-1978 Wilson,James,2000-02-29 Chen,Mei,31/12/1988 Brown,Olivia,07-14-1995 Ahmed,Omar,1967-11-08 Rossi,Sofia,06/03/2001 Taylor,Daniel,unknown Dubois,Emma,not-a-date ``` This Job reads ten fictional customer records and adds a standardized birth date for reporting. A separate output identifies records that require correction. The input schema contains one Dynamic column. The Fact column option identifies birthdate in that column without defining each CSV field in the schema. ## Linking the components ### Procedure 1. Create a Standard Job named StandardizeCustomerBirthdates. 2. From the Palette, add one tFileInputDelimited, one tStandardizeDate, and two tLogRow components. The two tLogRow components display accepted records and rejected records separately. 3. Connect tFileInputDelimited to tStandardizeDate using Row > Main. 4. Connect tStandardizeDate to the first tLogRow using Row > FLOW. 5. Connect tStandardizeDate to the second tLogRow using Row > Reject. ![Job layout with separate Main and Reject outputs from tStandardizeDate.](https://help.qlik.com/talend/en-US/components/8.0/Resources/images/use-case-tstandardizedate-job.png) ## Configuring the components ### Procedure 1. Click tFileInputDelimited to open its Basic settings view. 2. Enter the file path in File name/Stream or browse to the file. 3. Leave Use existing dynamic cleared. This Job discovers the fields from the CSV header without tSetDynamicSchema. 4. Click Edit schema and add one column named customer with type Dynamic. 5. Keep customer as the only schema column. The dynamic column contains last\_name, first\_name, and birthdate at runtime. Preserve the CSV header so the component can identify these fields. 6. Click tStandardizeDate and, in Basic settings, add a row to Date columns. | Setting | Value | | --- | --- | | Column | Select customer. | | Fact column | Enter birthdate, matching the CSV
header. | | Output pattern | Enter yyyy-MM-dd. | Do not enter customer as the fact column. The component keeps the original fields and adds a Date column named BIRTHDATE\_STANDARDIZED. ![tStandardizeDate configuration](https://help.qlik.com/talend/en-US/components/8.0/Resources/images/use-case-tstandardizedate-conf.png) 7. In Advanced settings, clear Use built-in patterns to parse source data. Explicit patterns make this example independent of the built-in pattern order. 8. In Additional patterns, add a row for each input pattern listed below. | Pattern | Example | | --- | --- | | "uuuu-MM-dd" | 1985-04-23 | | "dd/MM/uuuu" | 17/09/1992 | | "MM-dd-uuuu" | 12-05-1978 | 9. Keep Parsing locale as en and Time zone as UTC. 10. For Invalid date handling, select Reject invalid date. Keep Send unparseable data to reject flow selected. 11. Click the first tLogRow and, in Basic settings, click Sync columns. 12. Select Table to display the Main output. 13. Verify that the schema includes the Dynamic column, customer in this scenario, and the generated Date column, BIRTHDATE\_STANDARDIZED in this scenario. For date-only display, use yyyy-MM-dd as the generated Date column's schema date pattern. ![Output schema for the Main flow.](https://help.qlik.com/talend/en-US/components/8.0/Resources/images/use-case-tstandardizedate-output-schema.png) 14. Click the second tLogRow and, in Basic settings, click Sync columns. Select Table to display rejected records. The Reject schema includes REJECT\_REASON. ## Executing the Job ### Procedure Press F6 to save and run the Job. ### Results The Main output contains eight records. The component preserves customer names and original birth dates and adds the standardized Date values. ``` .----------------------------+----------------------. | tLogRow_1 | |=---------------------------+---------------------=| |customer |BIRTHDATE_STANDARDIZED| |=---------------------------+---------------------=| |Martin - Claire - 1985-04-23|1985-04-23 | |Patel - Arjun - 17/09/1992 |1992-09-17 | |Garcia - Elena - 12-05-1978 |1978-12-05 | |Wilson - James - 2000-02-29 |2000-02-29 | |Chen - Mei - 31/12/1988 |1988-12-31 | |Brown - Olivia - 07-14-1995 |1995-07-14 | |Ahmed - Omar - 1967-11-08 |1967-11-08 | |Rossi - Sofia - 06/03/2001 |2001-03-06 | ``` The Reject output contains Daniel Taylor and Emma Dubois, with a REJECT\_REASON explaining each parsing failure. ``` .--------------------------+-------------------------------------------------------------------------------. | tLogRow_2 | |=-------------------------+------------------------------------------------------------------------------=| |customer |REJECT_REASON | |=-------------------------+------------------------------------------------------------------------------=| |Taylor - Daniel - unknown |Column 'birthdate' value 'unknown' did not match any configured date pattern | |Dubois - Emma - not-a-date|Column 'birthdate' value 'not-a-date' did not match any configured date pattern| '--------------------------+-------------------------------------------------------------------------------' ``` **Related concepts** * [tStandardizeDate](https://help.qlik.com/talend/en-US/components/8.0/standardize/tstandardizedate "Standardizes date and timestamp values from selected input columns.") **Related information** * [tFileInputDelimited](https://help.qlik.com/talend/en-US/components/8.0/delimited/tfileinputdelimited) * [tLogRow](https://help.qlik.com/talend/en-US/components/8.0/logs-and-errors/tlogrow)