Subfield and string manipulation

[#1]

Hello community,

these are two records of a column (multiCategory) coming from a database:

Example:

row1 - > SW # SubCategory A,HW # SubCategory B,O # SubCategory C,Template # SubCategory X

row2 -> SW # SubCategory A

How can I put everything on the left side of the "#" into a column called "MainCategory" and set the right side to "SubCategory"

As you can see in row1 it is possible to have a comma seperated list indicating multiple main- and sub categories.

Expected possible values for row1:

MainCategory	Subcategory
SW	Subcategory A
HW	Subcategory B
O	Subcategory C
Template	Subcategory X

I started with below expression but it only works when there is one value like in row2.

if(IsNull(trim(left(multiCategory, index(multiCategory, '#')-1))),multiCategory, trim(left(multiCategory, index(multiCategory, '#')-1)))

Hope you can help me out.

Thank you.

[tresesco · Accepted solution]

Load
SubField(NewString,'#',1) as MainCategory,
SubField(NewString,'#',2) as NewCategory;
Load SubField(String,',') as NewString;
Load * Inline [
String
"SW # SubCategory A,HW # SubCategory B,O # SubCategory C,Template # SubCategory X"
"SW # SubCategory A"
]

PFA

[jonathandienst]

Hi

This is the script:

LOAD SubField(NVPair, '#', 1) As MainCategory,

  SubField(NVPair, '#', 2) As SubCategory

;

LOAD SubField(multiCategory, ',') As NVPair

INLINE

[

  multiCategory

  SW # SubCategory A,HW # SubCategory B,O # SubCategory C,Template # SubCategory X

  SW # SubCategory A

] (delimiter is '|')

;

See attached file which contains the output as requested.

Jonathan

[#4]

like magic 🙂 Thank you, that looks much easier than what I had in mind 🙂