subfield,left

[vignesh_s]

Product	Category	SubCategory
Edelweiss Tokio Group Credit Protection - SME	edelweiss tokio group	CREDIT PROTECTION
Edelweiss Tokio Group Credit Protection - SME - NEW	edelweiss tokio group	CREDIT PROTECTION
Edelweiss Tokio Life - Cash Flow Protection	edelweiss tokio life	CASH FLOW PROTECTION
Edelweiss Tokio Life - Protection - (Reg/5/10/15Pay)	edelweiss tokio life	PROTECTION
Edelweiss Tokio Life - Protection - (Reg/5/10/15Pay) - OLD	edelweiss tokio life	PROTECTION
Edelweiss Tokio Life - Protection - (Single Pay)	edelweiss tokio life	PROTECTION
Edelweiss Tokio Life - Protection  - (Single Pay) - OLD	edelweiss tokio life	PROTECTION
Edelweiss Tokio Life - Save n Prosper	edelweiss tokio life	SAVE N PROSPER
Edelweiss Tokio Life - Single Pay Endowment Assurance Plan	edelweiss tokio life	SINGLE PAY ENDOWMENT ASSURANCE PLAN
Edelweiss Tokio Life - Single Pay Endowment Assurance Plan - OLD	edelweiss tokio life	SINGLE PAY ENDOWMENT ASSURANCE PLAN

I have a product field as input ,i wanna split the product in cateory and sub cateory as shown above , can i have expresion fr tat

[Anil_Babu_Samineni]

I agree with Peter. Because, Your data is not proper structure as much i replied earlier having some structured and make sense to me. Will you conclude this?

[sunny_talwar]

Is there a master list which defines the possible values for Category and SubCategory. I mean there needs to be some way to identify what can possibly be a Category and what can possible be SubCategory.

[vignesh_s]

no there is no such list,from Product field i wanna split string in to category and sub category

[sunny_talwar]

But how do you do this? I mean if I give you this line in your Product field... what would you use to divide them out

My Name is Sunny and you are my friend

This is a very stupid example, but you get the idea... I can get infinite number of variation of data in Product column, how do I know what is category and what is Subcategory? Does it make sense?

[Or]

The product is 'Sunny' and the category is 'helpful person'

But back on the original track - there's no way to teach a computer to pick out the product and category if you, as a human, can't tell it how. If you don't know either, then your request is impossible - you can use assorted algorithms that involve text similarity to try and guess, but ultimately, it's just guesswork.

[vignesh_s]

one thing i tought of ,the 1st 3 words form Category and rest form sub category but in sub category v wanna eleminate some words

[sunny_talwar · Accepted solution]

May be try this

Table:

LOAD Left(Product, Index(Product, ' ', 3)-1) as Category,

Upper(SubField(SubField(Replace(LTrim(Replace(Right(Product, Len(Product) - Len(Left(Product, Index(Product, ' ', 3)-1))), ' - ', '  ')), '  ', ' - '), ' - ', 1), ' (', 1)) as SubCategory,

Product

FROM

[..\..\Downloads\String Task 1.xls]

(biff, embedded labels, table is Sheet1$);

[vignesh_s]

thanks a lot

[Anil_Babu_Samineni]

Liked It !!!

Sunny, One quick question - can you tell me why you are doing Replace() with -(hyphen) to space and again LTrim() to conclude for space to -(hyphen) ?? POS - I understand the concept of LTrim to use here but not sure why 2 replacements needed ??

[sunny_talwar]

Followed an approach similar to this to remove leading " - " in cases like these

QlikView Addict: Removing Leading Zeros