FUNCTION and FUNCTIONLOOKUP Functions
FUNCTION and FUNCTIONLOOKUP run a script that does a calculation over the result set and returns a single value for a column on a row.
You declare a return data type (the type of the function) and provide both an ETL script and a BQL query. The script is executed for each row of the result set. The variable [Result] is populated with the return value.
FUNCTION syntax:
FUNCTION(return_data_type,{parameters}, ETL_script, BQL_query)
FUNCTIONLOOKUP adds a lookup expression when iterating over a result set:
FUNCTIONLOOKUP(return_data_type, lookup_index, lookup_expression, script, BQL_query)
For example, to calculate the difference between two snapshot values (the sums as they would be on two different snapshots) use the following FUNCTION to traverse the dataset created by the BQL query and return a result.
FUNCTION(Float,
Dim [AmountIs] As Float = 0.0
Dim [AmountWas] As Float = 0.0
IF [SDate] Is Not Null THEN
IF DateDiff(Day,[SDate],DateTime(GetPromptValue('As of Date')))=0 THEN
[AmountIs]=Float([Amount]) + [AmountIs]
END IF
IF DateDiff(Day,[SDate],DateTime(GetPromptValue('Compare Date')))=0 THEN
[AmountWas]=Float([Amount]) + [AmountWas]
END IF
END IF
COMPLETE
[Result]=[AmountIs]-[AmountWas]
END COMPLETE,
SELECT TOP 100000 [Opportunity.Opportunity ID] 'OpID',[Total Historical Pipeline] 'Amount',[Opportunity_Snapshot Measures.Grade], [Snapshot Date.Snapshot Date] 'SDate', from [All] Where ([Snapshot Date.Snapshot Date]=GetPromptValue('Compare Date') OR [Snapshot Date.Snapshot Date]=GetPromptValue('As of Date')))
The previous example is not based on a row in the parent query. Use FUNCTIONLOOKUP to supply a lookup parameter so that it will run the function only on rows that satisfy the filter for the targeted column being equal to the value set.
The following example has a complex query that looks up both the current and previous snapshot date values for a given set of filters. In addition, the Opportunity ID from the parent query is passed in as a parameter and is used to filter the query. Based on this filtered result set, the function is run. It matches two rows, the as-is and as-was version of an opportunity. It then saves the results in variables. Then it runs complex if-then-else logic to determine how this change should be classified and returns a Varchar result.
FUNCTIONLOOKUP(Varchar(60),0,[Opportunity.Opportunity ID],
Dim [StageWas] As Varchar(60)
Dim [StageIs] As Varchar(60)
Dim [CloseDateWas] As DateTime
Dim [CloseDateIs] As DateTime
Dim [InPeriodIs] As Integer
Dim [InPeriodWas] As Integer
Dim [AmountIs] As Float
Dim [AmountWas] As Float
IF [Opportunity ID]=[OpID] AND [SDate] Is Not Null THEN
IF DateDiff(Day,[SDate],DateTime(GetPromptValue('As of Date')))=0 THEN
[StageIs]=[SStage]
[CloseDateIs]=[CDate]
[AmountIs]=[Amount]
END IF
IF DateDiff(Day,[SDate],DateTime(GetPromptValue('Compare Date')))=0 THEN
[StageWas]=[SStage]
[CloseDateWas]=[CDate]
[AmountWas]=[Amount]
END IF
END IF
COMPLETE
[Result]='Other'
[InPeriodIs]=0
[InPeriodWas]=0
IF [CloseDateIs] Is Not Null THEN
IF DateDiff(Day,DateTime(GetPromptValue('Period Start Date')),[CloseDateIs])>=0 AND DateDiff(Day,DateTime(GetPromptValue('Period End Date')),[CloseDateIs])<0 THEN
[InPeriodIs]=1
END IF
END IF
IF [CloseDateWas] Is Not Null THEN
IF DateDiff(Day,DateTime(GetPromptValue('Period Start Date')),[CloseDateWas])>=0 AND DateDiff(Day,DateTime(GetPromptValue('Period End Date')),[CloseDateWas])<0 THEN
[InPeriodWas]=1
END IF
END IF
IF [CloseDateIs] Is Not Null AND [CloseDateWas] Is Null THEN
[Result]='New'
ELSEIF [StageIs]='8. Implemented' AND [StageWas]<>'8. Implemented' AND [InPeriodIs]=1 THEN
[Result]='Won'
ELSEIF [StageIs]='Closed LostAND And [StageWas]<>'Closed Lost' AND [InPeriodIs]=1 THEN
[Result]='Lost'
ELSEIF [InPeriodWas]=1 AND [InPeriodIs]=0 THEN
[Result]='Moved Out'
ELSEIF [InPeriodWas]=0 AND [InPeriodIs]=1 THEN
[Result]='Moved In'
ELSEIF [AmountIs] Is Not Null AND [AmountWas] Is Not Null THEN
If [AmountIs]>[AmountWas] THEN
[Result]='Increased'
ELSEIF [AmountIs]<[AmountWas] THEN
[Result]='Decreased'
END IF
END IF
[StageIs]=Null
[StageWas]=Null
[CloseDateIs]=Null
[CloseDateWas]=Null
[AmountIs]=Null
[AmountWas]=Null
END COMPLETE,
SELECT TOP 50000 [Opportunity.Opportunity ID] 'OpID', [Opportunity Stage.Stage] 'SStage', [Opportunity_Snapshot Measures.Opportunity Close Date] 'CDate', [Opportunity_Snapshot Measures.Committed], [Sum: Historical Amount] 'Amount', [Opportunity_Snapshot Measures.Grade], [Snapshot Date.Snapshot Date] 'SDate' FROM [All] WHERE ([Snapshot Date.Snapshot Date]=GetPromptValue('Compare Date') OR [Snapshot Date.Snapshot Date]=GetPromptValue('As of Date')))
POSITION and SUBSTRING
You can also use FUNCTION to mimic Java's SPLIT string function using POSITION and SUBSTRING.
SplitString splits a string into tokens based on a delimiter.
FUNCTION [SplitString]([inputString] AS VARCHAR(255),[delimiter] as VARCHAR(1), [ZeroBasedReturnFieldNumber] as INTEGER) AS VARCHAR(255)
DIM [myListOfFields] AS List(Varchar(255))
DIM [myReturnValue] As Varchar(255) = 'Not Found'
DIM [aField] as Varchar(255) = ''
DIM [remainingString] as Varchar( 255 ) = [inputString]
DIM [fieldStartingPosition] as INTEGER = 0
DIM [delimiterPosition] as INTEGER = 0
WHILE [delimiterPosition] > -1
[delimiterPosition] = POSITION([delimiter],[remainingString])
IF [delimiterPosition] > -1 THEN
[aField] = SUBSTRING( [remainingString], 0, [delimiterPosition] )
[fieldStartingPosition] = [delimiterPosition]+1
Add([myListOfFields],[aField])
[remainingString]=SUBSTRING([remainingString],[fieldStartingPosition])
ELSE
Add([myListOfFields],[remainingString])
END IF
END WHILE
IF( [ZeroBasedReturnFieldNumber] < Length([myListOfFields] ) )
THEN
[myReturnValue] = [myListOfFields]([ZeroBasedReturnFieldNumber])
END IF
return [myReturnValue]
END FUNCTION
You can call it in the body of the scripted source:
INLINE TESTING
DIM [myDebugField] as VARCHAR(255) = 'unset'
[myDebugField] = [SplitString]( 'first field, second field, third field, fourth field', ',' , 0 )
print 'myDebugField = "' + [myDebugField] + '"'
print ''
Note: The last parameter, the field you want returned to you, is zero relative.