QCD Spreadsheet File Format

You will find here the details about the data that are sent to the service that updates QCD values (/v1/services/qcd/importfile) based on data sent in a CSV or XLS spreadsheet. This service is described in this other article. 

In summary, this service can update any indicator data in any iObeya board, at any date, with any accepted value, where: 

• The identification of iObeya elements is done through their names (room, board, etc.), and later by id or tag. 
• Multiple letters, board and rooms can be updated from data in a single file 
• Historical data can also be updated as the board period will be changed automatically to match the input dates. 
• The cell wedge number is automatically computed after the input date unless the target letter has a custom cut-out (not linked to dates, with custom wedges) 
• Ability to update letters with custom wedges 
• The data is read from XLSX or CSV file 

The file can be either CSV or XLSX, in the expected format, and it provides: 

• The list of individual indicators to update, in the form of an array of rows. 
• For each row, the names for: room, board, letter, circle (optional), wedge (optional) and indicator. 
• The target date: must be in YYYY-MM-DD (US) format, when written as text in CSV, or be of date type in an XLSX document. 
• The value itself: must be a numeric value, integer or float. 

The spreadsheet must be structured in the following way: 

• The first row must contain the title of the column (the column order does not technically matter) 
• Each of the subsequent rows must have all their mandatory cells populated with the appropriate value, to fully qualify a single indicator to update. Empty mandatory cells will generate an import error for that row. 

The following rules apply: 

• The import QCD service expects to have only elements names in the spreadsheet, by opposition to their technical and unique ids. 
• However, if roomId and/or boardId are specified in the service (http query) parameters, they would then override the corresponding values in the spreadsheet: the corresponding columns can then be left empty or even totally removed from the spreadsheet. 
• The titles for these columns have predefined default values and most of them can be redefined through different service parameter (see table below for details) 
• All names are case-sensitive and must exactly match the object names they refer to. 

Mandatory names: 

• Room name
• Board name
• Letter name
• Indicator name

Optional names: 

• The technical ring name, i.e. “inner”, “middle” or “outer” (mandatory for multi-circles letters, when the same indicator is used across different rings.

Other mandatory columns: 

• value: for the actual indicator numeric value 
• date: to specify the target date 

Optional column for letters with custom cut-out / custom wedges: 

• wedge: must then contain the numerical wedge number (1-53) when the letter contains custom defined circles, with user defined wedges.

The table below lists the expected columns, their meaning, their default title and the service parameter that controls the title name for each column. 

• Duplicate letter names: It’s not possible to update a letter if its board contains another letter with the exact same name (which happens when a letter is duplicated) 
• Circle names: it’s not possible to specify a circle by its user defined name, but only by using its exact internal or technical name:  

• • outer: this is the default ring (or circle) which does not need to be specified most of the time 
  • middle: indicates the second ring on a 2 rings letter, or the center ring on a 3 rings letter 
  • Inner: refers to the most central ring, only for 3 rings letters. 

• Wedge column name: there is no parameter at the service level to specify the title of the wedge column, that will always have to be the default value (wedge). 
• Wedge names: it is not possible to specify a wedge by its name, but only through its wedge number (integer) starting at 1 for the first wedge. 
• Number of rows: For performance and security reasons, the maximum length of the input file is limited to 10,000 rows, or indicators that can be updated in the same processing pass. Large files can significantly affect system performance during data processing and analysis. By enforcing this line limit, we ensure smoother operations and faster response times.
• Fixed names: The data is sensitive to iObeya elements names changes, whether it’s a room, a board, a letter or an indicator name: in this case, the data sent in the spreadsheet will have to also reflect these new names. Future versions will include options for specifying the iObeya target elements using either Ids (immutable) or tags (defined on the admin side) 

Single board, multiple letters

Here, we have three simple letters on the same monthly board, each with a single indicator but with a different period cutout. 

Letters:

Indicators:

Excel data (you can download an example of such a file here):

Results:

Multiple circles example

In the following example, we have a yearly letter, cut out in weeks, with an indicator called ‘week’ that is used across 3 circles, as shown in the images below.

If we want to send data for the same date, for the same indicator, but with different values for each circle, we need to format our file as follows (you can download a example of such file here):

The result would then be as expected:

Custom wedges cut-out example

For this example, we have set up a letter with 7 custom wedges, named as shown here:

In this case, to update the first 3 wedges, the input file would look like (example file available here): 

And the results would look like: