topic Re: Identify & store values in String separated by special character in QlikView

Identify & store values in String separated by special character

Wed, 26 Nov 2014 10:13:14 GMT

Hi,

I have a field category_name which may or may not have 4 sub-categories separated by '>>':

E.g.:

Column_Name (Category_name)

SPORTS_n_FITNESS>>Sports Bags

FASHION_ACCESSORIES>>Eyewear>>Hers

FASHION_ACCESSORIES>>Watches>>Swiss Watches>>His

GIFTING>>Indulgence

I want to separate these fields before and after '>>' and store it separately

Category_name Sub_Category_name_1 Sub_Category_name_2 Sub_Category_name_3 Sub_Category_name_4 SPORTS_n_FITNESS Sports Bags

FASHION_ACCESSORIES Eyewear Hers

FASHION_ACCESSORIES Watches Swiss Watches His

GIFTING Indulgence

It should be blank for No Value.

Can you please help me ?

Many Thanks

- Deep

Re: Identify & store values in String separated by special character

rubenmarin — Wed, 26 Nov 2014 10:24:07 GMT

Hi Deep, you can use subfield function:

LOAD Subfield(Category_name, '>>', 1) as Category_name,

Subfield(Category_name, '>>', 2) as Sub_Category_name_1,

Subfield(Category_name, '>>', 3) as Sub_Category_name_2,

Subfield(Category_name, '>>', 4) as Sub_Category_name_3,

....

Re: Identify & store values in String separated by special character

ashfaq_haseeb — Wed, 26 Nov 2014 10:24:52 GMT

Try like this

load SubField(Column_Name,'>>',1) as Category_name,
SubField(Column_Name,'>>',2) as Sub_Category_name_1,
SubField(Column_Name,'>>',3) as Sub_Category_name_2,
SubField(Column_Name,'>>',4) as Sub_Category_name_3
   ;
load * Inline
[
Column_Name
SPORTS_n_FITNESS>>Sports Bags
FASHION_ACCESSORIES>>Eyewear>>Hers
FASHION_ACCESSORIES>>Watches>>Swiss Watches>>His
GIFTING>>Indulgence
];

Regards

ASHFAQ

Re: Identify & store values in String separated by special character

Wed, 26 Nov 2014 10:35:59 GMT

Perfect it worked,

Many Thanks Ashfaq & Ruben