Lookup Records

Looks up number or text values from a source table to a destination table.

Permissions

Command Kind	Needed User Kind
Write	All

Parameters

Name	Type	Description	Required	Default	Values
LookupTable	string	Name of the lookup table ([Table]).	yes
Table	string	Name of the table ([Table]).	yes
Add	boolean	If 'True', the new value is added to an existing one. If 'False', the new value overwrites an existing one. The default value is 'False'.	no	false
Average	array of string	Builds the average of the lookup field and saves it in the specified field. ([Field].[LookupField].[Factor]). The default factor is '1'.	no
Cache	boolean	If 'True' a cache is used. It saves the lookup results for further use. The default value is 'True'.	no	true
Count	array of string	Builds the count of the lookup field and saves it in the specified field. ([Field].[LookupField].[Factor]). The default factor is '1'.	no
DateCalc	string	Aggregates date values: [None]=no aggregation, [Year]=jan. till dec., [YearToDate]=jan. till now, [EndOfYear]=dec., [Rolling]=12 months back till now, [OffsetYear]=whole offset year of the database, [OffsetYearToDate]=offset month of the database till now., [EndOfOffsetYear]=last offset month of the database.	no
DateOffset	integer	Moves the date dimension items by the offset. The default value is '0'. For example the month '2022-02' with an offset of 5 becomes '2022-07'.	no	0
Dimension	array of string	Dimension that is compared ([Dimension]). It must be present in both tables.	no
DimensionAttribute	array of string	Dimension that is compared by attribute ([Dimension].[Attribute]). Dimension is the dimension of the table. The attribute is the name of the lookup dimension. The attribute value the name of the item to be filtered.	no
DimensionString	array of string	Dimension that is compared by a text field ([Field]). The field name is the name of the lookup dimension. The text value the name of the item to be filtered.	no
Filter	array of string	Filters by dimension item ([Dimension].[Item]) or tree sum ([Dimension].[Tree].[Sum]). The tree sum format is equal to 'FilterTree'.	no
FilterAttribute	array of string	Filters by attribute ([Dimension].[Attribute].[Value]).	no
FilterField	array of string	Filters by field ([Field].[Operator].[Value]). The operators are (long and short): Contains (CO), Ends (EN), Equal (EQ), Greater (GR), GreaterEqual (GE), Less (LS), LessEqual (LE), NotContains (NC), Starts (ST) and Unequal/NotEqual (UE/NE)	no
FilterRange	array of string	Filters by an item range ([Dimension].[StartItem].[EndItem]).	no
FilterTree	array of string	Filters by tree sum ([Dimension].[Tree].[Sum]).	no
LookupFilter	array of string	Filters the lookup table by dimension item ([Dimension].[Item]) or tree sum ([Dimension].[Tree].[Sum]). The tree sum format is equal to 'LookupFilterTree'.	no
LookupFilterAttribute	array of string	Filters the lookup table by attribute ([Dimension].[Attribute].[Value]).	no
LookupFilterField	array of string	Filters the lookup table by field ([Field].[Operator].[Value]). The operators are: Contains (CO), Ends (EN), Equal (EQ), Greater (GR), GreaterEqual (GE), Less (LS), LessEqual (LE), NotContains (NC), Starts (ST) and Unequal/NotEqual (UE/NE)	no
LookupFilterRange	array of string	Filters the lookup table by an item range ([Dimension].[StartItem].[EndItem]).	no
LookupFilterTree	array of string	Filters the lookup table by tree sum ([Dimension].[Tree].[Sum]).	no
LookupSubtable	string	Name of the lookup subtable ([Subtable]). A subtable contains additional filters used by the lookup table.	no
Max	array of string	Builds the max of the lookup field and saves it in the field. ([Field].[LookupField].[Factor]). The default factor is 1.	no
Min	array of string	Builds the min of the lookup field and saves it in the field. ([Field].[LookupField].[Factor]). The default factor is 1.	no
Subtable	string	Name of the subtable ([Subtable]). A subtable contains additional filters used by the table.	no
Sum	array of string	Builds the sum of the lookup field and saves it in the field. ([Field].[LookupField].[Factor]). The default factor is 1.	no

Result

No fields

Code

LOOKUP RECORDS
LOOKUPTABLE=[string, Needed]
TABLE=[string, Needed]
ADD=[boolean, default=false]
AVERAGE=[string, Multiple]
CACHE=[boolean, default=true]
COUNT=[string, Multiple]
DATECALC=[string]
DATEOFFSET=[integer, default=0]
DIMENSION=[string, Multiple]
DIMENSIONATTRIBUTE=[string, Multiple]
DIMENSIONSTRING=[string, Multiple]
FILTER=[string, Multiple]
FILTERATTRIBUTE=[string, Multiple]
FILTERFIELD=[string, Multiple]
FILTERRANGE=[string, Multiple]
FILTERTREE=[string, Multiple]
LOOKUPFILTER=[string, Multiple]
LOOKUPFILTERATTRIBUTE=[string, Multiple]
LOOKUPFILTERFIELD=[string, Multiple]
LOOKUPFILTERRANGE=[string, Multiple]
LOOKUPFILTERTREE=[string, Multiple]
LOOKUPSUBTABLE=[string]
MAX=[string, Multiple]
MIN=[string, Multiple]
SUBTABLE=[string]
SUM=[string, Multiple]