IModelling.Coalesce

Syntax

Coalesce(Parameters: Array): Variant;

Parameters

Parameters. Array of series.

Description

The Coalesce method returns the series, each point of which is calculated as the first met value from specified series that is not equal to Null.

Comments

A calculation period can be set for the method. To do this, use the IModelling.SetPeriod method, by specifying it at any place of input array.

Variables in Parameters must be separated via comma.

For correct method work, make sure that the array of series contains more than one series.

If all values in the input array of series are set to Null, the method also returns Null.

To calculate the method without taking into account empty values, use the IgnoreMissing parameter by specifying it in any place of the input array. Available values of the parameter:

Consider the three series A, B and C. After executing the Coalesce method the following series will be obtained:

Series/Values                
A 1 Null 1 1 Null 1 Null Null
B 2 2 Null 2 Null Null 2 Null
C 3 3 3 Null 3 Null Null Null
Coalesce (A, B,C) 1 2 1 1 3 1 2 Null

Example

Executing the example requires that the repository contains a modeling container with the MS identifier. This container includes a model with the MODEL_D identifier that is calculated using the determinate equation method and contains more than two input variables.

Add links to the Metabase and Ms system assemblies.

Sub UserCoalesce;
Var
    Mb: IMetabase;
    ModelSpace, ModelObj: IMetabaseObject;
    Transf: IMsFormulaTransform;
    Formula: IMsFormula;
    Model: IMsModel;
    Determ: IMsDeterministicTransform;
    TransVar: IMsFormulaTransformVariable;
    Slice: IMsFormulaTransformSlice;
    TermInfo: IMsFormulaTermInfo;
    X1, X2, X3: String;
    Expr: IExpression;
Begin
    // Get repository
    Mb := MetabaseClass.Active;
    // Get modeling container
    ModelSpace := Mb.ItemById("MS").Bind;
    // Get the model
    ModelObj := Mb.ItemByIdNamespace("MODEL_D", ModelSpace.Key).Edit;
    Model := ModelObj As IMsModel;
    // Get model calculation parameters
    Transf := Model.Transform;
    Formula := Transf.FormulaItem(0);
    Determ := Formula.Method As IMsDeterministicTransform;
    // Get the first input variable
    TransVar := Transf.Inputs.Item(0);
    Slice := TransVar.Slices.Item(0);
    TermInfo := Transf.CreateTermInfo;
    TermInfo.Slice := Slice;
    // Set mode of passing variable into calculation
    TermInfo.Type := MsFormulaTermType.Pointwise;
    // Get internal view of the variable as a text
    X1 := TermInfo.TermInnerText;
    // Get the second input variable
    TransVar := Transf.Inputs.Item(1);
    Slice := TransVar.Slices.Item(0);
    TermInfo := Transf.CreateTermInfo;
    TermInfo.Slice := Slice;
    // Set mode of passing variable into calculation
    TermInfo.Type := MsFormulaTermType.Pointwise;
    // Get internal view of the variable as a text
    X2 := TermInfo.TermInnerText;
    // Get the third input variable
    TransVar := Transf.Inputs.Item(2);
    Slice := TransVar.Slices.Item(0);
    TermInfo := Transf.CreateTermInfo;
    TermInfo.Slice := Slice;
    // Set mode of passing variable into calculation
    TermInfo.Type := MsFormulaTermType.Pointwise;
    // Get internal view of the variable as a text
    X3 := TermInfo.TermInnerText;
    // Get model calculation expression
    Expr := Determ.Expression;
    Expr.References := "Ms";
    // Set model calculation expression
    Expr.AsString := "Coalesce(" + X1 + ", " + X2 + "," + X3 + ", True, SetPeriod(2000,2010))";
    // Check if the expression is correct
    If Expr.Valid
        // If the expression is set correctly, save the model
        Then ModelObj.Save;
        // If the expression is incorrect, display a message to the console window 
        Else Debug.WriteLine("Model is not saved: error in the formula");
    End If;
End Sub UserCoalesce;

After executing the example a series will be obtained using the Coalesce method to the first three input variables for the period from 2000 to 2010 without taking into account skipped values.

Example of Use in Expressions

Expression 1:

Coalesce(True, {Unemployment rate, %|Anchorage[t]}, {Unemployment rate, %|Chicago[t]}, {Unemployment rate, %|Mexico[t]})

Result: a series is obtained by applying the Coalesce method to the Unemployment rate, %|Anchorage, Unemployment rate, %|Chicago, Unemployment rate, %|Mexico series without taking into account skipped values.

Use: it can be used in formulas of cross functional expression editor in any platform tool where it is available.

Expression 2:

Coalesce(X1, X2)

Result: a series is obtained by applying the Coalesce method to the X1 and X2 factors.

Use: it can be used in model formulas of modeling container based on variables.

See also:

IModelling