Creating tables in Envision

Creating tables in Envision












Home » Resources » Here

While the most usual way to create a table in Envision consists of reading the table from flat files, Envision also offers the possibility to create tables by itself. More precisely, these created tables may be defined and instantiated within the script. Creating a table can be particularly useful when none of the input tables contain the desired properties sought for a specific calculation.

   Syntax overview
   extend.range()
   extend.distrib()
      Gap
      Multiplier
      Reach
   extend.billOfMaterials()


Syntax overview

The usual syntax for creating a table lies in the table and extend statements which are represented by the following syntax:
table T = extend.range(Orders.42)
Here, the keyword table should not be confused with the tile type, as in show table. Table T can then be used in the script, just like any regular table loaded from files.
show table "Number of lines" with sum(T.1)

extend.range()

The most simple way to create a table is to use the extend.range() range extension : for every line of the original table, N lines are created and numbered. The range extension can be useful when one is looking to introduce new lines into a table.

The syntax is as follows:
table T = extend.range(Orders.K)
T.Quantity = Orders.Quantity // implicit projection
show table "Line numbers" with T.N, T.Quantity
The argument is expected to be an integer, which may vary from one line to the next in the original table. This provides control on the granularity of the number of lines to be introduced.

Table T is typed as an extension of the table originally specified as an argument. Hence, as illustrated in line 2 above, plain cross-table assignments work because every line in T is still associated with its originating counterpart in Orders. The field T.N is populated by default and represents any given line number. This field starts at 1, and increases through +1 increments.

Beware, the extend.range() function can generate an arbitrarily large number of lines. First, generating an extremely large table is likely to be very slow; and second, depending on the processing quota in your Lokad account, it may not succeed. In practice, if K is greater than 10 over the average, then extend.range() might not be the correct solution to the problem you are trying to solve.

The following example illustrates how specific lines within a table can be duplicated, while preserving the content of the original table.
read "/sample" all
expect Grid[*]

table G = extend.range(Grid.Min == 0 ? 2 : 1)
G.Min = Grid.Min // affinity between 'G' and 'Grid'
G.Max = Grid.Max
G.Probability = Grid.Probability
G.Probability = 0 where G.N == 2
show table "My Grid" with Id, G.Probability, G.Min, G.Max

extend.distrib()

Envision distributions offer a powerful algebra, which can be used to avoid convoluted calculations of probabilities over lists. However, there are situations where having a raw list of probabilities is just fine, and even desirable. The distribution extension extend.distrib() turns a vector of distributions into a table, as illustrated by the following syntax:
table T = extend.distrib(D)
show table "Distribution details" with Id, T.Min, T.Max, T.Probability
The argument D is expected to be a vector of distributions, as typically produced by Lokad's probabilistic forecasting engine. Table T is typed as an extension of the originating tables, the implicit Items table in the script above. Table T is populated with three fields:

  • T.Min: the inclusive integral lower boundary of the segment
  • T.Max: the inclusive integral higher boundary of the segment
  • T.Probability: the sum of the distribution over the inclusive range

Despite its name, the Probability field actually refers to the sum of the distribution over the bucket range which is returned.

For relatively compact distributions, segments have a length of 1, and hence T.Min == T.Max. However, if the distribution spreads over higher values, segments of length equal to 1 would end up generating possibly millions of lines, which becomes unmanageable. Hence, when facing such large-valued distributions, Envision auto-aggregates the distributions around larger segments. Algorithms are therefore fine-tuned to keep the size of the generated tables manageable.

By design, extend.distrib() always singles out the zero segment. As a result, the [0;0] segment always gets its own line in the generated table. This behavior is indeed helpful in many business situations where zero demand represents an edge case - such as infinite stock cover - which requires some dedicated logic.

Then, three additional overloads for extend.distrib() are provided in order to gain more control over the specific granularity of the generated table.

Gap

The first overload is intended to help build a purchase prioritization list while taking the current stock levels into account. The syntax is the following:
table T = extend.distrib(D, S)
The first argument D is as defined above. The second argument S is expected to be an integral number. When this second argument is present, the generated table always includes two lines dedicated to the two segments [0;0] and [1;S]. Further segments are auto-generated starting from S+1 as detailed above. The default value for this argument when left unspecified is zero.

In practice, argument S is frequently defined as the sum of the stock available plus the stock on order. Indeed, when reordering, only the demand probabilities which exceed the current stock level should be considered.

Multiplier

The second overload is intended for situations where lot multipliers are involved. In these situations, the table should iterate over segments of specific sizes. The relevant syntax is:
table T = extend.distrib(D, S, M)
The arguments D and S are as defined above. The third argument M is expected to be an integral number. It represents the desired segment length. Thus, the table includes the list of segments [0;0], [1;S], [S+1;S+M] [S+M+1;S+2M] … If M is zero, then the function falls back on auto-sizing the segments.

In practice, forcing segments whose length is equal to 1 would possibly lead to performance issues as the size of the table can be arbitrarily large. Thus, Envision can fall back on a multiple of M instead. Using a multiple ensures that a lot multiplier logic will keep working; while preserving sane limits on the number of lines to be generated.

As a rule of thumb, we suggest not to use this overload unless lot multipliers are involved; and when they are involved, it is suggested to keep M at zero for any item that does not have a specific lot multiplier.

Reach

The third overload is intended for situations where MOQs are involved. In these situations, the table should iterate long enough to reach certain desired values. The relevant syntax is:
table T = extend.distrib(D, S, M, R)
The arguments D, S and M are as defined above. The fourth argument R is expected to be a non-negative integral number. It represents the desired max value to be reached by the grid, that is, there will be a line where T.Max is greater or equal to R. The default value for this argument when left unspecified is zero.

In practice, this argument is used to cope with large minimal order quantities (MOQs) constraints that can only be satisfied with the generated table iterates far each to cover the MOQ values.

As a rule of thumb, we suggest not to use this overload unless there are MOQs to be reached; and when they are involved, it is suggested to keep R as small as possible. A small R value does not prevent the table T from reaching higher values, it only ensure that larger values are reached.

extend.billOfMaterials()

The bill of materials covers situations where the interest lies not in the items that have been sold or served, but in their parts or composition. In these situations, it is desirable to translate a demand history at the “items” level into a demand history at the “parts” level. This translation is controlled through the bill of materials which specifies the composition of each item.

The extend.billOfMaterials() call function is precisely intended to cover this use case with:
table T = extend.billOfMaterials(
  Item: J.Id
  Part: B.PartId
  Quantity: B.Quantity
  DemandId: O.OrderId
  DemandValue: O.Quantity)

T.DemandDate by T.DemandId = same(O.Date) by O.OrderId // illustrate how to get the date

show table "Details" with Id, T.DemandDate, T.Quantity
Three tables - J, B and O - are expected. Table J is usually the items table, but this is not a strict requirement. Table B is intended to be the bill of materials table; and it’s expected to be an extension of Table J, i.e. of type (Id, *), when J happens to be the items table. Table O is expected to be the demand history, typically referring to the orders table. Table O is also expected to be an extension of Table J.

The resulting table is an extension of Table J, with proper affinities in place. Table T contains two fields that are already populated:
  • T.DemandId, which is intended to offer a way to identify in the table T the originating lines in the table O.
  • T.Quantity, which is obtained by rolling out the bill of materials.

Frequently, it is useful to inject a date into the table T. This can be done with a left-by statement as illustrated by the line below the billOfMaterials() block here above.

The extend.billOfMaterials() function supports recursive assemblies, that is, an item can appear both as a component and as a bundle within Table B. Naturally, the function fails with an error if cyclic dependencies are found in the bill of materials table.

The Quantity argument represents the number of units of B.PartId which are involved when selling or serving an assembly identified by J.Id.