Need help with removing data

[mgregory24479]

We have a field where in our DW it is a list agg field so we get multiple "rows" in one data item. Each item is usually separated by // but I have removed those so they show as follows. My next step is to remove the (YYYY) from each of the rows. I have tried a combination of a trim, substr, and instr as well as some other syntax expressions and have not figured it out yet. Any suggestions on working through this? Thank you so much in advance.

ABC Suite, Football 2015 (2016)
Dinner at ABC 11/5/2014 (2015)
ABC Donor Dinner 2010 (2010)
ABC Reception 9/20/2007 (2008)

[TomCognos]

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2336.htm

Try using instr to identify the " ("

Then use substring(abc,1,instring(abc," (")+1) to get start of first (YYYY). Then rinse and repeat. The below example assumes there is always 4 lines of code. You will have to adjust the numbers to account for removing the bracketed year. Start with one line and slowly add in lines until you are sorted.

substring(abc,1,instring(abc," (")+1)
+
substring(abc,instring(abc," (")+7, instring(abc," (")+1) )
+
substring(abc,instring(abc," (")+7, instring(abc," (")+1) )
+
substring(abc,instring(abc," (")+7, instring(abc," (")+1) )


Ideally you would want to split these out in your DWH but as your at this point i'm assuming this is not an option.

[mgregory24479]

Thanks for the response. Unfortunately, it is throwing an error when I replace the abc with the actual field name. I tried changing the " quotes you have listed with ' apostrophes thinking that might be the cause and it didn't correct it. I have attached a screenshot of the error. Do you have any other suggestions? Thanks

Here is what I tried first:

substring([Events_Formatted],1,instring([Events_Formatted]," (")+1)
+
substring([Events_Formatted],instring([Events_Formatted]," (")+7, instring([Events_Formatted]," (")+1) )
+
substring([Events_Formatted],instring([Events_Formatted]," (")+7, instring([Events_Formatted]," (")+1) )
+
substring([Events_Formatted],instring([Events_Formatted]," (")+7, instring([Events_Formatted]," (")+1) )

[TomCognos]

Ok. First of all don't do it this way. Please etl this. But since you asked and i currently have time on my hands. Here is how I did it.

12 Data items. The first two don't have a solve order, but from [Line1] until the last data item increment the solve order by 1 from 1 to 10.

Data items are as follows with the code contained within following

[Main String]
'ABC Suite, Football 2015 (2016)
Dinner at ABC 11/5/2014 (2015)
ABC Donor Dinner 2010 (2010)
ABC Reception 9/20/2007 (2008)'

[Main String Length]
character_length ([Main String])

[Line1]
substring([Main String],1,position(' (',[Main String])-1)

[Line1 Length]
character_length ([Line1])

[Rest of string minus Line1]
substring([Main String],[Line1 Length]+9)

[Line2]
substring([Rest of string minus Line1],1,position(' (',[Rest of string minus Line1])-1)

[Line2 Length]
character_length ([Line2])

[Rest of string minus Line2]
substring([Rest of string minus Line1],[Line2 Length]+9)

[Line3]
substring([Rest of string minus Line2],1,position(' (',[Rest of string minus Line2])-1)

[Line3 Length]
character_length ([Line3])

[Rest of string minus Line3]
substring([Rest of string minus Line2],[Line3 Length]+9)

[Line4]
substring([Rest of string minus Line3],1,position(' (',[Rest of string minus Line3])-1)

Caveats.
First of all if you text is on different lines as shown above, substring counts the carriage return as 1. If its all on one line you can change the 9's to an 8's
Secondly, you may have to tweak the functions that are available to you.
Thirdly, substring when you leave out the third argument it returns the rest of the string. Handy.
Finally if you have more than 4 lines, just repeat and increment the solve order. Though if you have less than 4 lines it may fail having gone over the boundary of the string.

So yes it can be done, but it's definitely not a preferred solution.

See my output attached. Apologies for the dev watermark.