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
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, " >> ")
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, " >> ")
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!!