topic Re: Subfield and string manipulation in QlikView

Subfield and string manipulation

Mon, 09 Feb 2015 12:20:25 GMT

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.

Re: Subfield and string manipulation

tresB — Mon, 09 Feb 2015 12:27:32 GMT

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

Re: Subfield and string manipulation

jonathandienst — Mon, 09 Feb 2015 12:51:29 GMT

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

Re: Subfield and string manipulation

Mon, 09 Feb 2015 13:50:48 GMT

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