Complex Expression on Matrix Data Load
I have a complex expression on the amount field in a data source used to convert any non-local currencies to the local currency (see below). That complex expression currently runs on tabular data wit...
Forum Discussion
Hi Joliver388,
Can you print args.line string and see how OneStream is processing that string I think it converts a matrix data source into a tabular in the backend and then process it accordingly I may be wrong but can you trying printing the arg.line line expression and share the results then what you can do is split that string into an array and select the value you want to check There will be 24 complex expressions for every time dimension.
And if the above isn't working you can write a connector which will parse the file convert it into tabular format from matrix and then you can simply use the complex expression you already have.
It will be great if you share args.line expression to work on this solution further.
Thanks,
Omkareshwar
Thanks for the suggestion. It doesn't look like the data is converted at this stage of the process. Below is an example of how the args.line looks. It's one row of data with the amounts in 25 different fields.
I may look into converting the data source to tabular but first wanted to see what other options exist to handle this for a matrix load.
| 10105,ACP001501,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5 |
You can attach your code to every time dimension which you have created and with args.value you can access the value on that dimension and with args.line you can access entire line from there you can fetch entity.
Your code should work with some modifications of col position rather than using a list of string split your args.line into an array.
Dim line as string = args.line
Dim currentLine() As String = line.Split(","c)
Can you try this and share your results to debug further.
Thanks,
Omkareshwar
Below is what I printed on the Time dimension. Sorry, this may be really basic, but I have a complex expression on the time dimension already to determine the time period to load to. Are you suggesting I could modify that expression to also translate the values I have in my first 24 fields? Those are the amounts that I want translated to another currency. I was thinking the complex expression would need to be on the amount field?
Description: argSs: -100, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 71910, , Salaries Taxes & Benefits, Direct Only, CAN, , 6, Flex2023For a Matrix data source, you don't have a single amount column rather you have multiple amount columns for every month.
How these complex expressions work is that when you import data from file to stage these rules will apply the logic while parsing data from the file.
so think of it in that terms
for a matrix data source, you have multiple amount columns OneStream uses the transformation rules to transform that data into tabular form to load to each month you need to setup your transformation rules properly for a matrix data source to work.
Thanks,
Omkareshwar
Archetype Consulting
On Matrix loads, expressions are evaluated once per file if put on the individual dimension element, and every time if set on Amount.
If you're just trying to determine which period each column is, try looking at this thread: https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Unable-to-get-value-from-another-column-in-Parser-BR/m-p/19099/highlight/true#M1340
Also look at the properties of the api object, there are a few matrix-specific fields you might want to check out.
