04-04-2023 06:16 PM - last edited on 05-25-2023 05:42 AM by JackLacava
Hi, I have a matrix data source matrixed on Account. Sample below. One of the Account fields is a Date that I need to convert to a Long to store. I can do the conversion but not sure how to target the value to convert when in a matrixed data source.
If it were a delimited data source I would use: Dim sDateField As String = api.Parser.DelimitedParsedValues(6). Is there a way to identify the value in a matrixed data source?
thanks
Greg
Code1 | Entity | Account1 | Account2 | Account3 |
Test1 | Entity1 | 1/1/24 | 0.025 | 0.5 |
Test2 | Entity2 | 5/1/24 | 0.025 | 0.5 |
Test3 | Entity3 | 3/1/25 | 0.025 | 0.5 |
Solved! Go to Solution.
04-07-2023 10:35 AM
Hi, we actually have over 300 Account columns in the source file with various data types. I was finally able to get this to work. First I had to ignore any columns with no data. Then before the return statement I had to add: api.ValueIsnumeric = True. thanks for all of the help.
04-05-2023 09:29 AM
You should be able to get your source value with args.Value, or the whole line with args.Line, or try to get the line in a dictionary with api.Parser.GetFieldValuesForSourceDataRow(si, api.RowGUID).
04-05-2023 10:15 AM
hi Jack thanks for the reply. I had tried args.value and args.line but those only return the header row (Args.Value=Account1; Args.Line=Code1, Entity, Account1, Account2, Account3) I'm not quite sure how to use GetFieldValuesForSourceDataRow but I can't return anything meaningful with it so far.
04-05-2023 10:39 AM
Sorry, just to clarify, is this DS a Fixed, Delimited, or Connector? I'm just trying to understand where you're running code.
04-05-2023 10:46 AM
I'm using a delimited file (.csv)
04-05-2023 11:48 AM - edited 04-05-2023 11:49 AM
Ahh, ok, so your real problem is that you're parsing the header line, which you should instead bypass. Once you fix that, you can just use Args.Value in a rule or Complex Expression applied to the column you want to change, and it will give you the necessary value.
The bypass will be automatic if the line doesn't contain something that looks like a number in the position you've mapped your Amount dimension to (which doesn't really matter anyway, since you have a matrix; but will still be used by the system to figure out if the line is valid). If that doesn't work, you can create an explicit Bypass rule, map it to a column (e.g. the Entity one), set it to "Contains at Position", "Entity", and it will work.
04-05-2023 03:06 PM
unfortunately args.value is still only pulling the Account names and not the values. I am putting the Rule on the Account1 column. It seems it is only pulling the related Dimension values. When I use args.line on the Amount, surprisingly I can at least see the string of values, however, I'm not sure how to isolate the conversion on just that Account.
04-06-2023 05:02 AM
Ah yes sorry, I was testing on the wrong dimension.
I guess the easiest approach is to run on Amount, detecting the date (e.g. if args.Value.Contains("/") then...) and returning the converted value.
I have a feeling this sort of thing could be done via Transformation Rules instead, matching the account name, but I'm even worse at those...
04-07-2023 10:16 AM
In your source filehow many Account columns? Did you create the same amount of Dim in your DataSource? Did you set up the Matrix Header Values Line# to 1 for each dimension/headers?Then did you do your transformation for all the account Headers?
Can you send us a picture of the right side of the Data Source? And an Account Dim print screen as an example?
04-07-2023 10:35 AM
Hi, we actually have over 300 Account columns in the source file with various data types. I was finally able to get this to work. First I had to ignore any columns with no data. Then before the return statement I had to add: api.ValueIsnumeric = True. thanks for all of the help.