Data source Complex Expression in Logical Operator

[ecarney]

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 ?

[RobbSalzmann]

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

[ecarney]

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) 

[RobbSalzmann]

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).

[JackLacava]

Matrix files are a bit of a different world. A few pointers:

• https://community.onestreamsoftware.com/t5/Rules/Complex-Expression-on-Matrix-Data-Load/m-p/20497/hi...
• https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Sign-Switch-on-Matrix-Data-...