03-08-2024 10:23 AM - last edited on 03-08-2024 12:40 PM by JackLacava
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 ?
03-08-2024 02:44 PM - edited 03-09-2024 11:01 AM
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
03-08-2024 03:00 PM
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)
03-08-2024 04:30 PM - edited 03-09-2024 11:00 AM
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).
03-09-2024 05:34 AM - edited 03-09-2024 05:35 AM
Matrix files are a bit of a different world. A few pointers: