Our ERP has several fields that have multiple values separated by a semi-colon. For example the Tax Amount field in the Order Header table holds the tax amounts for the state, county, city, other1 and other2 tax jurisdictions for a particular order presented like [43.12;6.84;5.13;0.00;0.00].
We used Hyperion before Phocus and it had a function called “split” that allowed me to create translated field for just one of the values. If I wanted the State tax amount I would use the formula “Taxamt.split(”;")[0]" , the County tax amount would be “Taxamt.split(”;")[1]", etc.
Does anyone know if Phocas has something similar?
Thanks!
Ted
Hi Ted,
The below may only work on a row by row basis - so you may need to be careful about how you set each variable. It may be something to contact your account manager about to see if it’s something we could help to deliver once we better understand, but the below shows there are ways of identifying values within a field. It may be best to create a user specific function that would evaluate this on an individual row basis.
Phocas is run on Microsoft SQL and if you were on the latest version there is a new function called split_string(). However I don’t think you are, but you could do this another way. I have created a SQL statement which should do as you need. The only thing you would need to do is update the references to look at the desired field and then call whichever one of those values you require. This does assume the format of the values will always be either 4 or 5 characters long (including the decimal), as well as always holding 5 vales within the field. It may also take some adjusting if there are any other caveats with the data, but it should enable you to retrieve the desired value. It works by first of all working out where each of the separators are and then using this to know where to extract the values from.
I hope it helps in someway.
declare @1 int
declare @2 int
declare @3 int
declare @4 int
set @1 = select charindex(’;’,[Taxamt]) From [DB].[Table]
set @2 = select charindex(’;’,[Taxamt],@1+1) From [DB].[Table]
set @3 = select charindex(’;’,[Taxamt],@2+1) From [DB].[Table]
set @4 = select charindex(’;’,[Taxamt],@3+1) From [DB].[Table]
Select
replace(Substring([Taxamt],1,5),’;’,’’) as [Amount1],
replace(Substring([Taxamt],@1+1,5),’;’,’’) as [Amount2],
replace(Substring([Taxamt],@2+1,5),’;’,’’) as [Amount3],
replace(Substring([Taxamt],@3+1,5),’;’,’’) as [Amount4],
replace(Substring([Taxamt],@4+1,5),’;’,’’) as [Amount5]
Regards,
Lee
Lee,
Good morning!
Thank you for the code! Yes, we are on SQL2012 we don’t have use of the string_split() function.
I am still at a pretty basic level with SQL syntax so I am sending you the SXE_Base file where this will be used (with the edits included) and an example of the data we are looking to parse.
There are actually two fields that need to be split - oeeh.taxauth (hdr.taxauth) and oeeh.taxamt (hdr.taxamt).
The data dictionary says the taxauth field is “CHAR x(6)” and the taxamt field is “DECI(4) zzzzzzzz9.99-“.
Am I headed in the right direction?
Thanks!
Ted