Excel Load Data source (fixed files data source) does complex expressions works ?
Hello I've tried to load my excel file through the data source fixed files with a complex expression on the time dimension, and i think it doesn't go through the complex expression at all so i'm wondering if i can use a complex expression in fixed file data source ? The check i'm trying to do is to verify the workflow period and the time that's in my load file. if it's the same it can continue loading but if it's different i'm asking OS to pop up a error message. If anyone has an idea please share it with me Thanks 🙂
Complex Expression on Matrix Data Load
I have a complex expression on the amount field in a data source used to convert any non-local currencies to the local currency (see below). That complex expression currently runs on tabular data with the time period (CurTime) I reference to pull the FX rate in one field. I am now wanting to have this same complex expression used on a Matrix data source with time across 24 fields. Any thoughts on how this could be done? Is there a Column Number api I could use to define CurTime? 'This is to look to see if the entity is a non local USD entity and convert the USD data to Local Dim fields As List(Of String) = StringHelper.Splitstring(args.Line,",") Dim curEntity As String = fields(2) Dim UKEntityDim As OneStream.Shared.Wcf.Dim = BRApi.Finance.Dim.GetDim(si, "iFinDept") Dim UKEntityDimTypeID As Integer = UKEntityDim.DimPk.DimTypeId Dim CurEntityID As Integer = BRApi.Finance.Members.GetMemberId(si,UKEntityDimTypeID , curEntity) Dim CurEntityLocCur As String = BRApi.Finance.Entity.GetLocalCurrency(Si, CurEntityID).Name If CurEntityLocCur.XFEqualsIgnoreCase("USD") Then Return Args.Value Else 'Get Workflow Scenario ID Dim wfScenarioName As String = ScenarioDimHelper.GetNameFromID(si, si.WorkflowClusterPk.ScenarioKey) Dim wfScenarioId As Integer = ScenarioDimHelper.GetIdFromName(si, wfScenarioName) 'Pull the Current Time that we are looking at Dim CurTime As String = fields(1) 'Brapi.ErrorLog.LogMessage(si, args.line.tostring) Dim FxRateType As String = BRApi.Finance.Scenario.GetFxRateTypeForRevenueExpense(si, wfScenarioId).Name Dim FxRatePkUsingNames As New FxRatePkUsingNames FxRatePkUsingNames.DestCurrency = "USD" FxRatePkUsingNames.SourceCurrency = CurEntityLocCur FxRatePkUsingNames.Time = CurTime FxRatePkUsingNames.FxRateType = FxRateType Dim FxRate As Decimal = BRApi.Finance.Data.GetStoredFxRate(si, FxRatePkUsingNames).Amount If FxRate <> 0 Then Return args.Value / FxRate End If End Ifsign flip in complex expression
When loading data, all of our accounts from source match the names of the account in OneStream so we've always used a *:* rule for the account maps. However, we have a new dataset coming in and we need to flip the sign for those accounts that are of account type Revenue in OneStream: not assets or anything else, just revenue. I have working code that will conditionally do something if it's a revenue account else pass through... but I don't know what that something should be to flip the sign. Other people have posted questions about how to do a sign flip in a complex expression but there are no answers. I definitely do not want to maintain a manual list of 1,500 1:1 rules that do nothing but flip the sign. The complex expression feels like the only scalable solution but this detail is missing.
Dimension Look-up during Transformation
We are trying to develop logic to do the following during transformation: If the G/L account in the load is in a certain range and the UD2 member is valid, load to that intersection otherwise If the G/L account in the load is in that same range but the UD2 member is invalid, map UD2 to None Has anyone gotten something similar to work? Thanks, BilNew Syntax for LookupRowFieldValue()
Summary Is there any documentation and/or sample code showing how to use the new "dbWheres" argument in a LookupRowFieldValue() function? Detail In version 6.8 (maybe earlier), if you use .LookupRowFieldValue() with your criteriaExpression (i.e. - your where clause) as a string you get a warning that this syntax is obsolete. It still runs, but obviously we shouldn't use it going forward. So, something like this now throws an warning: .LookupRowFieldValue(si, dbLocation, tableName, criteriaExpression, fieldToReturn, defaultValue) .LookupRowFieldValue(si, dbLocation.Framework, "SecUser", Name = 'Phil Brosnan', "Email", String.Empty) OneStream wants you to use a new Syntax that replaces the criteriaExpression with a list of where clauses in a dbWheres object. .LookupRowFieldValue(si, dbLocation, tableName, dbWheres, fieldToReturn, defaultValue) Unfortunately, there is no documentation or examples on what a dbWhere object is, how to create one, or how to pass that dbWhere object into your dbWheres list. My question is, does anyone know how to use this new “dbWhere” syntax in a LookupRowFieldValue function, or know how to do something similar with a different function? Just looking for some sample code I can leverage. Thanks,
