Forum Discussion

MarkHoughton's avatar
MarkHoughton
Contributor
9 months ago

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

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 ...
Transformation
Krishna's avatar
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 .

 

MarkHoughton's avatar
MarkHoughton
Contributor
9 months ago

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

  • Krishna's avatar
    Krishna
    Valued Contributor
    9 months ago

    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

     

  • MarcusH's avatar
    MarcusH
    Contributor III
    9 months ago

    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.

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    9 months ago

    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)