Articles in this section

4. Configure Measure Mapping (ByColumn or ByCell Mode)

data1.io Team
  • Updated
Follow

With the ByColumn or the ByCell Mode you define, on which value fields (Measure01, Text01, Num01, etc.) of the fact table the values from a workbook is written.

Overview

The ByColumn Mode is the most commonly used mode in data1.io. As in the Workbook Wizard can be seen, all values (= cells) of a column in the workbook are mapped with a single entry in the ByColumn definition row to a value field (MEASURE, TEXT, NUM, etc.) in the fact table:

The ByCell Mode is used in specific cases. As in the Workbook Wizard can be seen, every value (= cell) of the workbook is mapped to a value field (MEASURE, TEXT, NUM, etc.) in the fact table:

 

ByColumn Mode

The ByColumn Mode uses the following 2 named ranges:

  • data1_CELLS01_ByColumnMapping
    Valid Entries:
    MEASURE01_0 = Mapping on the MEASURE01 field in the fact table
    TEXT01_0 = Mapping on the TEXT01 field in the fact table
    NUM01_0 = Mapping on the NUM01 field in the fact table
    DATE01_0 = Mapping on the DATE01 field in the fact table

  • data1_CELLS01_ByColumnMapping_Records
    Valid Entries:
    + = the value is saved on a separate record
    * = the value will be added on an existing record in the same row (if available)

Demo:

 

ByCell Mode

The ByCell Mode needs a named ranges for every interactive cell in the CELLS0x area:

  • data1_CELLS0x_MEASURE01_1 = Mapping on the MEASURE01 field in the fact table
  • data1_CELLS0x_MEASURE02_1 = Mapping on the MEASURE02 field in the fact table
  • ...
  • data1_CELLS0x_TEXT01_1 = Mapping on the TEXT01 field in the fact table
  • data1_CELLS0x_TEXT02_1 = Mapping on the TEXT02 field in the fact table
  • ...
  • data1_CELLS0x_NUM01_1 = Mapping on the NUM01 field in the fact table
  • data1_CELLS0x_NUM02_1 = Mapping on the NUM01 field in the fact table
  • ...
  • data1_CELLS0x_DATE01_1 = Mapping on the NUM01 field in the fact table
  • data1_CELLS0x_DATE02_1 = Mapping on the NUM01 field in the fact table

Demo:

 

Relative Record Number

The relative Record Number is more important for the ByCell Mode, but it can also be used in the ByColumn Mode. It is appended to the value mapping as a suffix:

  • ByColumn = MEASURE01_0
  • ByCell = data1_CELLS01_MEASURE01_1

With the relative Record Number you can control, which information is combined on the same record and which information is safed on a separate record.

Related articles