How can I ( if possible ) set a UD static value from a data source based on contents of a column

MarkHoughton
Contributor

Hi,

I have a data source, where the leading character of the alphanumeric values in the column are used to identify if the row value should be assigned to a particular dimension field in a UD. The complex expression here will display results in the error log indicating it has found the rows that I want to assign a different value for in the UD, but I don't know how to set that , so that the imported data assigns my UD requirement. Basically I want it so that if the args.value starts with "R" then assign it a value of "R" in the UD2 otherwise, leave it/set it as "None".

Dim sValue As String = args.Value
Dim myValue1 As String = "UD2 R-" & sValue
Dim myValue2 As String = "Ignore"

If Left(svalue,1)="R" Then
    BRApi.ErrorLog.LogMessage(si, myValue1)
Else
    BRApi.ErrorLog.LogMessage(si, myValue2)
End If

 

Any thoughts or am I approaching this the wrong way ? I cannot see that I would do this in a transformation rule as it is the source GL code that carries this information.

Cheers all

 

5 REPLIES 5

Krishna
Valued Contributor

Based on your code I explained it. You can perform it in Import step or Validate Step. The below is import step. 

Import Step

1. If you are doing at the Import Step, then args.value will store the value in the string. All you need to do is to Return the value. Curently you are printing it. 

2. Transformation rule you have to map the respective UD dimensions.

 

Validate Step 

You can use the Dim sValue As String =  args.GetSource("U1#"). It will get the source value of the U1 and you can map it .

 

Thanks
Krishna

Hi Krishna,

Sorry, I am not really understanding what you are suggesting. What I have in my import file is a GL code prefixed with an "O", "X" or "R" and then a 6 digit number. What I want to do is based on that leading letter "O","X" or "R" is to assign a value to UD2 of "O","X", or "R".  I cannot do it in transformation rules as "O123456" and "R123456" map to the same OneStream Accounts code, but I want to differentiate them using UD2. So I am not sure of the correct syntax to write to UD2 using a data source complex expression.

What I can do is edit the source file before importing and run a script on that to assign the O,R, or X but would like for this to be done within the data source rule in OneStream ( if possible ).

Thanks

If I understand what you want to do correctly I would add a Business Rule to the Data Source for UD2. That BR takes the first character of the Account member. Then in Transformation Rules for UD2 map R to whatever you want and everything else to None.

My personal opinion is to put this in the transformation rules, rather than modifying the data source at import.

  • This keeps your data source 'pure', meaning it will look the same in OneStream's import view as it does in any regular query.
  • It also gives you more flexibility to modify the U2 transformation rules should you need it (maybe you'll need R123* instead of just R* in the future, for example).

To put this in the transformation rule, set your UD2 transformation to a Type:Composite

Rule Name Description Rule Expression Target Value
R Rule1 R Rule1 A#R*:U2#* R
O Rule1 O Rule1 A#O*:U2#* O
Future example R123 R Rule123 A#R123*:U2#* R123

The above assumes your O123456 and R123456 are from the account dimension, but you can modify according to your needs.

Cheers    -db

Edit: I assumed you have a different column that is determining the R/O/X mapping; if the data source is the U2 and you just need to drop the numbers you'd use a Mask type with R* to R.

Edit: Reference material: Mapping Types (onestream.com)

Hi @MarkHoughton - Sorry for the confusion. Here we go. It is a Parser Business Rule. and you can attach it to the UD2 member in your datasource. 

 

								'Accounts - If the Account is the first column in your file 
				Dim Acct As String() = args.Line.Split(",")
				Dim AcctStr As String = Acct(1).Trim
				Dim UD2Final As String = String.Empty
				
				If AcctStr.StartsWith("R") Then
					
					UD2Final = "R"
					
				Else If AcctStr.StartsWith("X") Then	
				
				UD2Final = "X"
				
				
				Else If AcctStr.StartsWith("O") Then	
				
				UD2Final = "O"
				
				Else
					
					'Other 
					
				End If

				Return UD2Final

I assume the below you are trying to achieve

Krishna_0-1712701683332.png

 

Thanks
Krishna