Skip to main content
E
ecarney
March 8, 2024

Data source Complex Expression in Logical Operator

  • March 8, 2024
  • 4 replies
  • 0 views

I have 2 Datasource imports that I want to use the same mapping tables.  The first workflow is an actual monthly GL load.   The datasource is a comma delimited file and uses 2 different columns to determine the source value for the UD1 dimension.   The second Datasource will use an Excel Matrix to Load Budget data for multiple periods.  There is logic in the first Datasource to use the Account source value instead of the UD1 source column as defined by position.  This is based on the value in the Account source field.  See the code below:

Dim Accountcol As String = args.Value
'Reminder - (2) column is 3th column in excel - need to count "0" as a digit
Dim CostCentercol As String = api.Parser.DelimitedParsedValues(6)
'Identify PL Accounts (greater than 39999)
If Accountcol > 39999 Then
	'Need to bring in Cost Center
	Return CostCentercol
Else
	'Use Account as UD2 source
	Return Accountcol
End If

My question is  How do I create a datasource using an excel Matrix load that refers to the Columns defined with A# and UD2# ?   Is there a method similar to api.Parser.DelimitedParsedValues(6) that I can code into the Matrix based Datasource  to test the source value coming from the Excel cilumn value ?

4 replies

R
RobbSalzmann
March 8, 2024

While not the complete answer, your current code may not evaluate correctly for accountCol.  Consider the following update:

Dim Accountcol As String = args.Value
'Reminder - (2) column is 3rd column in excel - need to count "0" as a digit
Dim CostCentercol As String = api.Parser.DelimitedParsedValues(6)

Dim AccountcolValue As Integer
'Attempt to convert Accountcol to an integer
If Integer.TryParse(Accountcol, AccountcolValue) Then
    'Identify PL Accounts (greater than 39999)
    If AccountcolValue > 39999 Then
        'Need to bring in Cost Center
        Return CostCentercol
    Else
        'Use Account as UD2 source
        Return Accountcol
    End If
Else
    Return Accountcol
End If

 

E
ecarneyAuthor
March 8, 2024

This  code works for a comma delimited file.  What I am asking is for is a way to parse a Matrix excel spread sheet where the Column for UD2 is defined using  UD2# in the first row of the named range.  the  delimitedparsevalue above if fine for predefined  file layout but how can the data source be informed about which data element  associated with the dimension (UD2) 

 

R
RobbSalzmann
March 8, 2024

It probably seems to work but you're relying on VB.net Coercion to force a string to be evaluated as an integer in the line:

 

Dim Accountcol As String = args.Value
...
If Accountcol > 39999 Then

 

Eventually you may see errors and might not understand them.  VB is a strongly typed language and only variables of like type can be compared.  You may not be doing the conversion, but its happening.  By not coding it yourself, you get whatever conversion the machine decides is right for you.  It may not always be what you want (hence the name Coercion).

B
BRujawitz
June 3, 2025

This is the code that worked for me:  I used IC and Entity and assumed that they should not match. I also added an example if two pre transformed entities map to one entity in OneStream.  This works for Delimited files.

Dim ICcol As String = args.Value.Trim()
Dim Entcol As String = api.Parser.DelimitedParsedValues(1).Trim()
'Column 2 holds the Entity information - you must subtract 1 from column value for the Complex Expression

' Convert both to uppercase for case-insensitive comparison
Dim ICcolUpper As String = ICcol.ToUpper()
Dim EntcolUpper As String = Entcol.ToUpper()

If (ICcolUpper = EntcolUpper) _
    Or ((ICcolUpper = "XXXXX" And EntcolUpper = "YYYYY") _
    Or (EntcolUpper = "XXXXX" And ICcolUpper = "YYYYY")) Then

    Return "None"
Else
    Return ICcol
End If

 

R
RobbSalzmann
June 4, 2025

BRujawitz​ your code is a bit brittle.  Consider using built in String.Equals() and employing StringComparison instead of creating new strings through converting strings to upper case, also, remember to use sort circuit operators (AndAslo, OrElse) wherever possible in logical evaluations:

Dim ICcol As String = args.Value.Trim()
Dim Entcol As String = api.Parser.DelimitedParsedValues(1).Trim()

If String.Equals(ICcol, Entcol, StringComparison.OrdinalIgnoreCase) _
    Or (String.Equals(ICcol, "XXXXX", StringComparison.OrdinalIgnoreCase) AndAlso String.Equals(Entcol, "YYYYY", StringComparison.OrdinalIgnoreCase)) _
    Or (String.Equals(Entcol, "XXXXX", StringComparison.OrdinalIgnoreCase) AndAlso String.Equals(ICcol, "YYYYY", StringComparison.OrdinalIgnoreCase)) Then

    Return "None"
Else
    Return ICcol
End If

Finally, I would refactor the above for readability (self documenting code) and proper null checking to help avoid unforeseen runtime exceptions as follows: 

Dim rawIC As String = args?.Value
Dim rawEnt As String = api?.Parser?.DelimitedParsedValues(1)

' Safely trim and default to empty string if null
Dim ICcol As String = If(rawIC, "").Trim()
Dim Entcol As String = If(rawEnt, "").Trim()

Dim isSameEntity As Boolean = String.Equals(ICcol, Entcol, StringComparison.OrdinalIgnoreCase)
Dim isSwapPair As Boolean = _
    (String.Equals(ICcol, "XXXXX", StringComparison.OrdinalIgnoreCase) AndAlso String.Equals(Entcol, "YYYYY", StringComparison.OrdinalIgnoreCase)) _
    Or (String.Equals(ICcol, "YYYYY", StringComparison.OrdinalIgnoreCase) AndAlso String.Equals(Entcol, "XXXXX", StringComparison.OrdinalIgnoreCase))

If isSameEntity OrElse isSwapPair Then
    Return "None"
Else
    Return ICcol
End If

 

Terms & ConditionsAccessibility statement