Formato del file di input per la previsione delle scorte - Software di ottimizzazione

Input file format for inventory forecasting


Home » Risorse » Qui

In order to deliver inventory forecasts, Lokad can import tabular data formatted as plain text files or Excel sheets. Tables should follow the Lokad guidelines. Files should be stored within the Lokad account. In practice, Lokad supports many third party apps and the data files are auto-generated by Lokad itself, directly pulling the data from the apps themselves. However when no such built-in support is provided, one has to fall back on manually producing the files to to consumed by Lokad. This document details the format used for inventory forecasting.


Big picture

In order to generate a report, Lokad expects at least two tabular files:
  • Lokad_Items contains the list of SKU / barcode / references / products to be processed.
  • Lokad_Orders contains the list of sales / orders / consumption's, that is the observed historical demand.

Those two files are expected to contain tabular data: the first line containing the column headers while the following lines contain the actual data. Lokad supports most plain text formats such as CSV (Comma-separated values), TSV (Tab-separated values) and even Microsoft Excel sheets - as long the Excel workbook follows a simple tabular form. Lokad tries to load files with the following extensions: .tsv .csv .xlsx .xls .txt. Lokad is also tolerant to various date or number formats.

An optional file Lokad_Parts may also be provided with the list of kits / bundles / bill of materials / assemblies. Also another optional file Lokad_Settings may contain inventory settings overrides.

In the following, we detail the column names expected for both items and orders. Most of the commerce management software can produce flat file exports. However, those exports are unlikely to follow the column naming patterns of Lokad. For example, the order quantity is expected to be named Quantity in Lokad, while maybe it's just Qty in your system. In order to address this problem, you can use an Envision script to preprocess your flat files and perform all the necessary adjustments.

Sample files

Download sample files

The content of the sample Lokad_Items.tsv file looks like this:

Id	LabelName	ServiceLevel	LeadTime	StockOnHand	StockOnOrder	
1	Candy Canes 200g	0.95	3	181	80
2	Orange Stick Candy 100g	0.95	3	400	200
3	Lemon Saf-T-Pops 200g	0.95	3	222	200
4	Caramel Squares 500g	0.95	3	121	50
5	Orange Sweethearts 100g	0.9	7	1498	680

You may add more label and tag columns to this file, as described in the documentation below.

The content of the sample Lokad_Orders.tsv file looks like this:

Id	Date	Quantity
1	2013-07-01	174
1	2013-07-02	96
1	2013-07-03	271
1	2013-07-04	335
1	2013-07-06	72

File naming rules

Lokad attempts to load the files of the specified input folder (which can be adjusted from the project configuration page) based on a set of rules related the file names. In particular, Lokad attempts to load any file that has a name starting with either Lokad_Items, or Lokad_Orders, or Lokad_Parts or Lokad_Settings. Then, the file extension must be once of the supported extensions with .tsv, or .csv, or .txt, or .xlsx, .xls. The file extension can also additionally include a .gz if the file has been GZipped (see section below Dealing with large files).

Flat file masking

When files are generated by a file import tool like Sync, there are situations where one would like to override the content of a specific file. Manually editing one of the flat file generated by the tool may not be very practical because the file is likely to be regenerated at the next data refresh, overwriting whatever changes had been originally brought to the file. Thus, Lokad supports a feature named file masking that precisely provides a way to persistently override the output of a file import tool.

Let's refer to the base filename as the filename without its extension suffix (eg. Lokad_Items for Lokad_Items.tsv). Whenever two detected files are varying only by their base filename, if one of the two files happens to be an Excel file, then only the Excel file is loaded by Lokad, while the other file is ignored. The Excel sheet is masking its flat file counterpart.

Tip: If you wish to mask a given flat file, go to the Files tab within your Lokad account, navigate down the folders, click on the desired file and select Download as Excel. You can modify this Excel sheet, as long the tabular format is preserved. Then, reupload the Excel sheet to Lokad, putting the file in the same folder. That's it, your revised Excel sheet is now masking the original flat file.

Fields of Lokad_Items

The file Lokad_Items contains the list of items to be forecast. In particular, if there are 3 stores to be forecast with 1000 references per store, this file should contain 3x1000=3000 lines.

Lokad_Items
Idstring required The item identifier (demand history will be aggregated against this identifier). Each line should have a unique identifier.
LabelFoostring optional Property to be displayed in the Excel report. See Label fields below.
TagFoostring optional Product attribute, used by the Forecasting Engine to improve the forecasting accuracy. See Tags Fields below.
TagLabelFoostring optional Property that behaves both as a Label and as a Tag.
StockOnHandnumeric optional The count of items that are physically available. No optimized reorder quantity can be computed if neither StockOnHand nor StockAvailable is provided. This field is mostly ignored when StockAvailable is present (see below), however, in practice, it's still relevant to include this field, as only this quantity can be checked against the "real" inventory quantity on shelf.
StockAvailablenumeric optional The count of items that are readily available to be serviced. Unlike the StockOnHand, this quantity takes into account units that have already been sold but not picked yet, hence that are not available to be serviced anymore. No optimized reorder quantity can be computed if neither StockOnHand nor StockAvailable is provided.
StockOnOrdernumeric optional The count of items that have already been reordered, but that are not yet present.
LeadTimeinteger optional The total number of days that need to be covered by the stock. See also our lead time tutorial. No quantile forecast and no quantile grid can be computed without this value.
LeadTime2integer optional Applies only to the calculation of quantile grids. Represent an alternative lead time that should get its alternative grid of quantiles. When present, a field named Probability2 is added to the Lokad_Grid output file.
LeadTime3integer optional Same as LeadTime2. Idem but with a Probability3 field in the output.
ServiceLevelnumeric optional The desired probability of not experiencing a stock-out. The value should be comprised between 0 and 1 (exclusive). See also our service level tutorial. This value is required for quantile forecasting, but not for quantile grid, the latter being like a quantile forecast for all possible service levels at once.
LotMultiplierinteger optional This field reflects a potential constraint on the lot size when reordering. This field is only used when quantile forecasts are used. It indicates the rounding multiple that should be applied to the order quantity as suggested by Lokad. When omitted, the default value is 1. Ex: if LotMultiplier=10 then Lokad only suggests multiples of 10 for reorders such as 10, 20, 30, etc.

The list of fields to be treated as labels or tags can now be defined in the configuration page of a forecasting project. Thus, prefixing many of your fields with Tag or Label is no longer a requirement, and can be done from the user interface of Lokad.

Label fields

Any field prefixed by the token Label is inserted as an extra column in the Excel report produced by Lokad. For example, if your TSV file contains a field named LabelProductName, then the report includes a column named ProductName (the Label prefix being removed from the name).

This convention allows to add any arbitrary set of extra fields for reporting purposes. Fields typically include, without limitations: Supplier, Brand, ISBN, etc. These fields are for display only and they do not impact the forecasting operation itself.

Tag fields

Any field prefixed with the token Tag is leveraged by Lokad to improve the forecasting accuracy. Family, subfamilly, color, size, brand ... are typical examples of tags. The field value for a tag can be empty: a tag value does not have to be provided for each item.

If you wish to combine both tag and label behaviors for a single field, then you need to prefix the column with TagLabel.

Fields of Lokad_Orders

The file Lokad_Orders contains the list of all client orders that have consumed the inventory in the past. For best accuracy, it is advised to provide a daily aggregated history (not weekly or monthly). The suggested depth of history is 3 to 5 years when the data is available.

Lokad_Orders
Idstring required Matches the Id field of the Lokad_Items files (foreign key).
Datedatetime required The date associated with the order quantity.
Quantitynumeric required The number of items ordered.

Fields of Lokad_Parts

Sometimes, the granularity of inventory inputs (goods received) does not exactly match the granularity of the inventory outputs (goods served). Such situations occur when kits, bundles, assemblies are present; or when goods are produced based bills of materials for example.

For example, when a kit is sold, there is one corresponding line recorded in Lokad_Orders. However, as the inventory optimization happens not at the kit level but at the part level, the kit line in Lokad_Orders must be replaced by several lines corresponding to the parts that constitute the kit.

The file Lokad_Parts is optional.

Lokad_Parts
Bundlestring required The target item to replaced by its parts from an inventory optimization viewpoint. Matches the Id field of the Lokad_Items files (foreign key).
Partstring required One of the parts of the bundle. Matches the Id field of the Lokad_Items files (foreign key).
Quantitynumeric optional The number of parts included in the bundle. If the field is omitted, then the value is 1.

Lokad does support recursive bundles, that is, bundle-of-bundles situations. Beware however that if a circular dependency is found, the data import of Lokad will fail.

Fields of Lokad_Settings

The file Lokad_Settings is primarily intended for scenarios where it is Lokad itself who generates the Lokad_Items file through one of our built-in integrations. In this situation, the file Lokad_Settings can be used to override and/or complete the data retrieved by Sync. In practice, there is little reason to use this file if you happen to be in control of the logic that generates Lokad_Items in the first place. However, for the sake of completeness, we document Lokad_Settings below.

The file Lokad_Settings is optional.

Lokad_Settings
Idstring required The target item whose inventory settings will be overridden. Matches the Id field of the Lokad_Items files (foreign key).
ServiceLevelnumeric optional A floating number between zero and one (exclusive) which represent the desired probability of not getting a stock-out. If this field is left empty, then the original items value is preserved.
LeadTimeinteger optional An integer representing a duration in days, as applicable to compute the demand forecast. If this field is left empty, then the original items value is preserved.
LotMultiplierinteger optional An integer representing the applicable multiple for the reorder quantities. If this field is left empty, then the original items value is preserved.

Renaming columns

Headers must match the column names described above. Headers that do not match anything are ignored. If your file contains the right headers but with different names, you can have Lokad rename them.

In order to do that, you will need to compose an Envision script.

Dealing with large files

In case of really large files, you can compress them and/or split them into multiple files. This applies for both Lokad_Items and Lokad_Orders.

Compression

In order to conserve disk space and also increase transfer speed, you can optionally compress files into archives one by one. In this case:

  • Each archive MUST contain only 1 file.
  • Deflate compression must be used (for instance, using GZIP utilities).
  • Archive name should end with .gz (ex: Lokad_Orders.tsv.gz).

Splitting

In case of really large files (still above 100MB once compressed), it is also recommended to split them files into separate smaller files. Splitting files allow incremental data upload, but also makes it easier to parallelize both uploads and downloads. The name of a flat file pushed to Lokad comes into 4 distinct parts, let's consider Lokad_Orders_20150131.tsv for example:

  1. Lokad: the prefix indicating that files follows the Lokad formatting guidelines.
  2. Orders: the middle part indicating the type of data; Items, Orders and Parts are supported.
  3. 20150131: an optional arbitrary suffix used for file splitting purposes.
  4. tsv: the flat file format extension, tsv and csv being supported.

All the files that only vary based on their suffix are concatenated by Lokad; that is, Lokad starts by merging all those files as if they were only a single file. Naturally, for the concatenation to succeed, all files are expected to have the same headers. In the example above we use a compact date to illustrate the suffix, but the suffix can be any alphanumerical string.

It is also possible to combining file splitting with file compression. For example, a list of files Lokad_Orders_20150129.tsv.gz, Lokad_Orders_20150130.tsv.gz, Lokad_Orders_20150131.tsv.gz can represent compressed daily increments of the order data.

TSV as the recommended format

Lokad supports a wide range of tabular formats such as TSV, CSV or even Microsoft Excel, as long the data is strictly tabular. Lokad has been designed to be very tolerant to various date and number formats, so you do not need to worry too much about those. However, if you happen to be a software developer, then we recommend to follow the guidelines below. Indeed, while Lokad might succeed in parsing a wide variety of formats, if you follow those guidelines, then it's guaranteed to succeed.

The recommended format for Lokad is TSV (Tab-separated Values). While the TSV format is old, it works surprisingly well for large datasets. TSV format is defined by:
  • A text file that consists of lines encoded in UTF8.
  • Each line contains fields separated from each other by TAB characters (horizontal tab, HT, ASCII control code 9).
  • Each line return is encoded either with \n (line feed, control code 10) or \r\n (control code 13 the carriage return, then line feed).
  • Field means here just any string of characters, excluding TABs. Indeed, each TABs divides the line into distinct field values.
  • Each line must contain the same number of fields.
  • The first line contains the names for the fields (on all lines), i.e. column headers.

Since TAB is used as a separator between fields, a field cannot contain a TAB. However, TABs usually don't appear in data items that you wish to tabulate, so this is seldom a restriction. Otherwise, we suggest to strip them upon export, replacing with 4 space characters.
More detailed description (along with instructions to export from Excel) is available: Tab Separated Values (TSV): a format for tabular data exchange

Numbers and dates

Floating numbers must be formatted using . (dot) as decimal separator. No other digit separators are allowed.

  • Good: 1234.00
  • Good: -12
  • Bad: 1234,12
  • Bad: 1.123,12
  • Bad: 1,123.12

Dates should be formatted using yyyy-MM-dd pattern, where:

  • yyyy - 4 year digits
  • MM - 2 month digits
  • dd - 2 day digits

For example:

  • Good: 2012-09-18
  • Bad: 2012-9-18
  • Bad: 9-18-2012
  • Bad: 18-9-2012