05-03-2023 04:08 PM
OneStream V7.0.1
We have a Data Source that takes data file that has 12 data columns Oct ... Sep.
The year for the data in the 12 data columns is a field "Fiscal Year", the first column of the data file.
We have a Parser BR that is called on each of the 12 data columns that looks at the first field in each record to get the year and concatenates that with the M<number> month for each month column to give the correct Time dimension member in which to store the data. e.g. 2023M1
The problem is we're getting the row header "Fiscal Year" instead of the year value in the record, so the string being returned looks like Fiscal YearM1
How do we get the year data from the first column instead of the column header?
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As ParserDimension, ByVal args As ParserArgs) As Object
Dim fiscalYear As String() = {"Oct","Nov","Dec""Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"}
Dim YearColPosition As Integer = 0
Dim year As String = String.Empty
Dim month As String = String.Empty
Try
'Both of these keep giving us the column header "Fiscal Year"
'instead Of the value In the currently parsed record
year = api.Parser.DelimitedParsedValues(YearColPosition)
'year = args.Line.Split(",")(YearColPosition)
'this marries the index of the month name in the months array
'with M to give us the M1 notation for period.
month = $"M{Array.IndexOf(fiscalYear, args.Value)+1}"
BRApi.ErrorLog.LogMessage(si, $"Time: {year}{month}")
Return $"{year}{month}"
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Solved! Go to Solution.
05-03-2023 06:23 PM
This approach should work
The result should automatically concatenate year and period for the exploded records.
05-04-2023 09:39 AM
This and the following code in a BR called by each of the month columns solved for this requirement. Thanks @JackLacava @ckattookaran and @franciscoamores for all the considerations.
05-03-2023 06:23 PM
This approach should work
The result should automatically concatenate year and period for the exploded records.
05-04-2023 09:39 AM
This and the following code in a BR called by each of the month columns solved for this requirement. Thanks @JackLacava @ckattookaran and @franciscoamores for all the considerations.
05-03-2023 07:11 PM
I think I've this rule somewhere 🤣🤣 I was not happy with the split the rule and then go by the index. I've to look this up and see if I still have it somewhere where you can lookup a value by giving the column header.
05-04-2023 08:36 AM - edited 05-04-2023 08:40 AM
It was a struggle to remember where it was. Luckily I thought of blogging about it and found it in my drafts. (almost a year later 🤣)
Dim dimensionKey As String = String.Empty
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
' Layout name is nothing but the name of the data source
Dim dbwIDParserWheres As New List(Of DbWhere) From {New DbWhere("LayoutName", DbOperator.IsEqualTo, "SalesPlanning_Temp")}
Dim layoutKey As String = BRApi.Database.LookupRowFieldValue(si, "App", "ParserLayouts", dbwIDParserWheres, "UniqueID", "")
' we need to get the dimension key from the parserdimensions table
' key for the sorteddictonary is as follows
' datatype\displayorder\dimensionname
If Not String.IsNullOrEmpty(layoutKey)
Dim dbwParserDimWheres As New List(Of DbWhere) From {New DbWhere("LayoutKey", DbOperator.IsEqualTo, layoutKey), New DbWhere("DimensionName", DbOperator.IsEqualTo, "Ic")}
dimensionKey = BRApi.Database.LookupRowFieldValue(si, "App", "ParserDimensions", dbwParserDimWheres, "Concat(Concat(Concat(Concat(DimensionDataType, '\'),DisplayOrder), '\') , DimensionName)", "")
End If
End Using
' get IC dimension of current line, location is mapped to IC
Dim icDim As ParserDimension = api.Parser.Dimensions.Item(dimensionKey)
Dim locationCode As String = icDim.Value
I've updated it to use the DbWhere clause.
05-04-2023 08:58 AM
Hi Celvin,
dimension keys and all other parser layout details is also available in the transformer object.
Api.Parser.Dimensions is a dictionary which has ParserDimension as value. In that object I think we can find all details for the line being processed.
There is also api api.Parser.GetFieldValuesForSourceDataRow(si As SessionInfo, rowID As Guid) wew you can pass the rowID to get a dictionary with all values from source row. RowID is stored in the ParserDimension object.
05-04-2023 09:03 AM
Yes it does. You can see that almost to the end of the code. The code above is to get the key if the ParserDimension. Now the Gersource one needs a guid and to get the guid, you need the same dance.
05-04-2023 09:08 AM
Sure but I mean you don't need to query the database as that information is already in the Parse object.
api.Parser.Dimensions will have all keys.
We used similar approach to get all data source entries for source id:
Dim parserDimSourceIdKeys As List(Of String) = objTransformer.Parser.Dimensions.Keys.Where(Function(x) x.EndsWith("si")).ToList
05-04-2023 09:16 AM
Ahhh, that is a good idea. Because the key end with the dimension name. Oh yes. There you go then there is no need for a DB query then.
05-04-2023 09:03 AM
I like the possibility of this: api.Parser.GetFieldValuesForSourceDataRow(si As SessionInfo, rowID As Guid)
I prefer to keep the logic in the single BR than spread it out over other columns.
05-04-2023 09:08 AM
Not sure what you mean by keeping it in the same BR, the code above is going to go. I'm not sure how you can get the row guid.
05-04-2023 09:13 AM
This should work:
api.Parser.GetFieldValuesForSourceDataRow(si, api.RowGuid)
That returns dictionary
05-04-2023 09:21 AM
I serialized the dictionary to Json and wrote it to the log to see what's inside. Its empty. As @JackLacava points out, it seems nothing row oriented is populated in a matrix rule. IMHO, this could be where we most need that functionality.
05-04-2023 09:31 AM
as I mentioned, would need to some test myself. I was just guessing 🙂
i can see if i can reuse something from golf stream...
05-04-2023 09:09 AM
That might work, but I would argue that the performance implications of doing two expensive db calls on every single record might give you issues at scale.
05-04-2023 09:12 AM
It is true. Unless there is a way to add that to globals and get it from there.
05-04-2023 09:15 AM
You can put in globals but I think all details needed in the parser BR should be available via api (ParserDimension object) and args (ParserArgs)
05-04-2023 02:40 AM
I'd need to do some testing but you could assign year column to attribute and then use it to concatenate in BR assigned to Time. To get Mx you can use replace expression in Time dim, similar to this:
05-04-2023 08:14 AM
Francisco! 🙂
We thought about using an attribute also, but are unable to figure out which object the attribute is hiding in in the BR, or if the BR even has access to it. Maybe you will find it in your testing.
Back in the days of Upstream, the record being operated on was one of the arguments of the parsing/transformation function. Its odd how that design didn't follow here. It made things very easy having the entire record available while parsing, as it's common to apply logic using the contents of multiple columns.
05-04-2023 08:34 AM
It does work like that, just not with Matrix loads.
05-04-2023 08:41 AM
@JackLacava any suggestion on how to get a value from another column in a matrix rule?
05-04-2023 08:50 AM
Line being processed is in args.Line. You can also use api functions to read data source setup in case that's needed.
Let's do something, can you reproduce this for GolfStream app so I can have a look there.
You will have to attach or send data source a sample file 🙂
05-04-2023 08:53 AM
He's working with a Matrix, those won't populate args.Line in the same way. It shouldn't be a problem, because values can be automatically concatenated like I mentioned above.
05-04-2023 08:50 AM
Did you try this? https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Unable-to-get-value-from-an...
As long as you put that extra Time field before all the other ones in the sequence, it will work (tested in 7.4).
Beyond that, you can set a rule on the Amount field; that will run for all values, with a populated line in args.Line.