Coalesce(Parameters: Array): Variant;
Coalesce(Parameters: System.Array; Context: Prognoz.Platform.Interop.Fore.ForeRuntimeContext): object;
Parameters. Array of series.
Context. Context. The parameter is used only in Fore.NET.
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.
A calculated 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:
True. Calculation does not take into account empty values.
False. Default value. Calculation takes into account empty values.
Consider three series - A, B and C. After the Coalesce method is applied, the following series is 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 |
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, 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;
Imports Prognoz.Platform.Interop.Ms;
Imports Prognoz.Platform.Interop.ForeSystem;
…
Public Shared Sub Main(Params: StartParams);
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 := Params.Metabase;
// 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.mfttPointwise;
// 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.mfttPointwise;
// 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.mfttPointwise;
// Get internal view of the variable as a text
X3 := TermInfo.TermInnerText;
// Get model calculation expression
Expr := Determ.Expression;
Expr.References := "Ms;Stat";
// 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 System.Diagnostics.Debug.WriteLine("Model is not saved: error in the formula");
End If;
End Sub;
After executing the example a series is obtained using the Coalesce method to the first three input variables for the period from 2000 to 2010 without taking into account skipped values.
Expression 1:
Coalesce(True, {Unemployment rate, %|Anchorage[t]}, {Unemployment rate, %|Chicago[t]}, {Unemployment rate, %|Mexico[t]})
Result: a series is obtained using the Coalesce method to the series: Unemployment rate, %|Anchorage, Unemployment rate, %|Chicago, Unemployment rate, %|Mexico without taking into account skipped values.
Use: it can be used in formulas of calculated series of time series database and in formulas of attribute-based models of modeling container.
Expression 2:
Coalesce(X1, X2)
Result: a series is obtained using the Coalesce method to the X1 and X2 factors.
Use: it can be used in model variable-based formulas of modeling container.
See also: