Is there any way to simplify this query?

[rberman]

I have a data field categ_hierarchy in the format of a series of up to 8 category IDs joined by ">>". For example:

categ_id1>>categ_id2>>categ_id3>>...>>categ_id8 

Category id 1 is required but category ids 2 through 8 are optional.  Category Ids are strings without the '>' cahracter in them. They might have whitespace that I want to trim from the beginning or end. 

Here are 2 examples:

simulink/index>>simulink/simulink-environment>>simulink/programmatic-modeling

support/parallel-221>>parallel-computing/index

I want to lookup each category id and get back the associated category name for each category id and then reconstruct the category names into a similarly formated path: 

categ_name1>>categ_name2>>categ_name3>>...>>categ_name8 

Here are the two examples constructed from the lookup results

Simulink >> Simulink Environment Fundamentals >> Programmatic Model Editing

Parallel Computing >> Parallel Computing Toolbox

Is there any way to simplify my SPL from this? 

| rex field=category_hierarchy "(?<categ_id1>[^>]+)(>>(?<categ_id2>[^>]+))?(>>(?<categ_id3>[^>]+))?(>>(?<categ_id4>[^>]+))?(>>(?<categ_id5>[^>]+))?(>>(?<categ_id6>[^>]+))?(>>(?<categ_id7>[^>]+))?(>>(?<categ_id8>[^>]+))?"
| eval categ_id1=trim(categ_id1), categ_id2=trim(categ_id2), categ_id3=trim(categ_id3), categ_id4=trim(categ_id4), categ_id5=trim(categ_id5), categ_id6=trim(categ_id6), categ_id7=trim(categ_id7), categ_id8=trim(categ_id8)
| lookup category_lookup category_id AS categ_id1 OUTPUTNEW category_name AS categ_name1
| lookup category_lookup category_id AS categ_id2 OUTPUTNEW category_name AS categ_name2
| lookup category_lookup category_id AS categ_id3 OUTPUTNEW category_name AS categ_name3
| lookup category_lookup category_id AS categ_id4 OUTPUTNEW category_name AS categ_name4
| lookup category_lookup category_id AS categ_id5 OUTPUTNEW category_name AS categ_name5
| lookup category_lookup category_id AS categ_id6 OUTPUTNEW category_name AS categ_name6
| lookup category_lookup category_id AS categ_id7 OUTPUTNEW category_name AS categ_name7
| lookup category_lookup category_id AS categ_id8 OUTPUTNEW category_name AS categ_name8
| eval category_name_hierarchy=categ_name1
| eval category_name_hierarchy=if(isnull(categ_name2), category_name_hierarchy, category_name_hierarchy." >> ".categ_name2)
| eval category_name_hierarchy=if(isnull(categ_name3), category_name_hierarchy, category_name_hierarchy." >> ".categ_name3)
| eval category_name_hierarchy=if(isnull(categ_name4), category_name_hierarchy, category_name_hierarchy." >> ".categ_name4)
| eval category_name_hierarchy=if(isnull(categ_name5), category_name_hierarchy, category_name_hierarchy." >> ".categ_name5)
| eval category_name_hierarchy=if(isnull(categ_name6), category_name_hierarchy, category_name_hierarchy." >> ".categ_name6)
| eval category_name_hierarchy=if(isnull(categ_name7), category_name_hierarchy, category_name_hierarchy." >> ".categ_name7)
| eval category_name_hierarchy=if(isnull(categ_name8), category_name_hierarchy, category_name_hierarchy." >> ".categ_name8)
| table category_hierarchy, category_name_hierarchy

I know I could split the category_hierachy field by the ">>" delimeter but I don't know how to lookup each of the category Ids in the resulting multivalue field. 

Any help would be appreciated!!

Thanks,

Rena

[johnhuang · Accepted solution]

Something like this:

| rex field=category_hierarchy "(?<categ_id1>[^>]+)(>>(?<categ_id2>[^>]+))?(>>(?<categ_id3>[^>]+))?(>>(?<categ_id4>[^>]+))?(>>(?<categ_id5>[^>]+))?(>>(?<categ_id6>[^>]+))?(>>(?<categ_id7>[^>]+))?(>>(?<categ_id8>[^>]+))?"

| foreach categ_id* [
	| eval output_name=null() 
	| eval "<<FIELD>>"=TRIM(<<FIELD>>)
	| lookup category_lookup category_id AS "<<FIELD>>" OUTPUT category_name AS output_name 
	| eval category_name_hierarchy=mvappend(category_name_hierarchy, output_name)
]
| eval category_name_hierarchy=MVJOIN(category_name_hierarchy, " >> ")

[rberman]

Thank you so much!! I had looked into foreach but I guess I didn't know how to use it properly. I really appreciate the help!!