Forum Discussion

ecarney's avatar
ecarney
New Contributor
10 months ago

Data source Complex Expression in Logical Operator

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 ?

Data Sources
  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    10 months ago

    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's avatar
    ecarney
    New Contributor
    10 months ago

    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's avatar
      RobbSalzmann
      Valued Contributor II
      10 months ago

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