The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Matrix Data Load - Data Source Setup
I have a Matrix data file with 36 Months of data. The header has the periods in MM/DD/YYYY format. Is it possible to convert this Format to OS Period format like 2023M1 and return in each of the 36-t...
Hi vmanojrc30 !
Yes you can Transform that in OneStream.
Before I go further, please have a look at HoustonBudget datasource in Golfstream! It will surely help.
Do do that you will need 36 Time timensions in your source.
Then you will need to define the Line of your header - the matrix hearder values line.
After that you will need to add a Transformation for your time dimension that will transform each time member into a onestream member.
Just for your testing purpose, i would say, keep it simple and try to do that only with 2 time columns!
Have a look at the printscreen below.
Please provide a kudo if it helps!
Nic
String-mangling dates is part of the .Net Framework. You can do it yourself like this:
As a one-liner:
Return $"{Convert.ToDateTime("2/1/2023").Year}M{Month(Convert.ToDateTime("2/1/2023"))}"
Yeilds: "2023M2"
In an XFBR:
Dim colHeaderDate As String = "11-Feb" 'taking 11-Feb from your screenshot above
Dim convertDate As String = $"20{colHeaderDate}-01"
Dim dateTime As DateTime = Convert.ToDateTime(convertDate)
Dim osDate As String = $"{dateTime.Year}M{Month(dateTime)}"
BRApi.ErrorLog.LogMessage(si, osDate)
Thanks Rob!
I am looking for ways to read the calendar date ("2/1/2023") from the header and do the conversion. Because the calendar date in header is not static. It Changes every month.