During Transformation, Tide needs to identify the header row in a Bordereau. To do this, Tide will use the header row details provided in the relevant Bordereau Questionnaire‍. This will operate differently, depending on whether you entered a single or range (e.g. rows 2-4) of header row(s). 

Select header for answering Questionnaire

When completing step 3 in the Questionnaire, Tide will ask you to select the header row(s) in the sheet you selected in the first question. This is used to identify the header for the purposes of answering the Questionnaire. You can select one row, or a range of rows. 

Single Header Row

If a single header row was entered during the completion of the Bordereau Questionnaire, then this row is saved as the Default Expected Header Row. During processing, Tide will first try and find the header row in this position. If it cannot, then Tide will look within the first three widest rows in turn (starting with the first widest row) in the Bordereau, as this is typically a reliable way of identifying the header row. If it cannot find the header row there, then Tide will raise an error. You can edit the Default Expected Header Row at anytime by editing the relevant Bordereau Questionnaire and navigating to the Sheet and Header step.

During processing, Tide will automatically identify the header, and so it does not then need to be in the same header row position and the columns do not need to be in the same order. The exception to this rule is if there are duplicate column names, e.g. there are 2 'Amount' columns. In which case they must be in the same column order.

Multiple Header Rows

If selecting a range of rows, then Tide will concatenate the values together when presenting the columns for answering questions. For example, if rows 2-4 are selected, then if in column K, 'Insured Details' is in row 2, and 'Name' is in row 3, then Tide will present 'Insured Details Name' as a column available for selection.

Where a range of header rows are selected, then Tide will always look to these exact row positions during processing. If Tide cannot find the header row in this position, then an error will be raised. You can edit the expected range of rows at anytime by editing the relevant Bordereau Questionnaire and navigating to the Sheet and Header step.

If there are merged cells in the header rows, then Tide un-merges the columns before reading the rows; this means that values in merged cells will be read only from the top left cell of the merged cells.

Duplicate Header Column Names

If Tide detects that a header row(s) selected have columns with the same title/name (e.g. Tax Amount appears in columns X and Z), you will be required to confirm that you are happy for Tide to additionally reference each column by its reference too (e.g. Column - A). Tide will then display all columns with their reference appended to the end (e.g. Insured Name [X]).

This then fixes the Questionnaire as using duplicate column names. During processing the columns must be presented in the same exact column position (as Tide cannot identify which column of the same name should be assigned to relevant Tide field). 

If a subsequent version of a Bordereau contains duplicate columns where it did not so previously, then you will need to complete a new Questionnaire that will then accommodate the duplicate column names.

Selecting Headers after Sheet Reassessment

When reassessing the Bordereau sheet used in a Questionnaire, you must upload the Bordereau file you wish to use by selecting the Reassess Sheet or Copy Questionnaire button in Step 3. Once the new Bordereau has been uploaded, the modal will present the same questions as present in Sheet & Header step. Once complete, you can update the Questionnaire mappings using the columns present in the new Bordereau/header. 

If a column was used previously, but is not in the new header, then it will be removed and will not be available for assignment to questions/Tide fields. This may mean questions are unanswered. In the case of mandatory Tide fields, a new column must be mapped before the Questionnaire can be saved. Similarly, sheet reassessment may have introduced new columns in the header. These new columns will be available for mapping in the Questionnaire.

Note – if there is any aggregation present in your Questionnaire, this will be removed if reassessing sheets.