matrix parser rule to change date to long

GregHertling
New Contributor III

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
1 ACCEPTED SOLUTION

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.

View solution in original post

9 REPLIES 9

JackLacava
Community Manager
Community Manager

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).

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.

Sorry, just to clarify, is this DS a Fixed, Delimited, or Connector? I'm just trying to understand where you're running code.

I'm using a delimited file (.csv)

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.

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.

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...

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?

NicolasArgente_0-1680876940461.png

Can you send us a picture of the right side of the Data Source? And an Account Dim print screen as an example?



Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

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.