Identify & store values in String separated by special character

[#1]

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

[rubenmarin · Accepted solution]

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,

....

[ashfaq_haseeb]

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

[#4]

Perfect it worked,

Many Thanks Ashfaq & Ruben