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
MarkHoughton's avatar
MarkHoughton
Contributor

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

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)