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

[Or]

Perhaps I'm missing something in the original, but there doesn't seem to be any consistent way to determine what category / subcategory to assign each product to. If you can write out an explanation either in English or in pseudocode, it'd be easier to help convert that to QlikView syntax.

[vignesh_s]

i have a Product Called 'Edelweiss Tokio Group Credit Protection - SME' in out i wanna Split the product as

'Edelweiss Tokio Group' as Category and 'Credit Protection' as Cateory type

[Anil_Babu_Samineni]

Perhaps this like attached? I know this is weird behavior? But output looks okay

[vignesh_s]

Can i get the expression for this

[Anil_Babu_Samineni]

Here we go

LOAD *, SubField(SubField(Product, '-',1),' ',1) & ' ' & SubField(SubField(Product, '-',1),' ',2) & ' ' & SubField(SubField(Product, '-',1),' ',3)  as Category,

If(SubStringCount(Product, 'SME'), Upper(SubField(SubField(Product, '-', 1),' ', -3) & ' ' & SubField(SubField(Product, '-', 1),' ', -2)),Upper(SubField(mid(Product, Index(Product,'-')+1),'-',1))) as SubCategory Inline [

Product

Edelweiss Tokio Group Credit Protection - SME

Edelweiss Tokio Group Credit Protection - SME - NEW

Edelweiss Tokio Life - Cash Flow Protection

Edelweiss Tokio Life - Protection - (Reg/5/10/15Pay)

Edelweiss Tokio Life - Protection - (Reg/5/10/15Pay) - OLD

Edelweiss Tokio Life - Protection - (Single Pay)

Edelweiss Tokio Life - Protection  - (Single Pay) - OLD

Edelweiss Tokio Life - Save n Prosper

Edelweiss Tokio Life - Single Pay Endowment Assurance Plan

Edelweiss Tokio Life - Single Pay Endowment Assurance Plan - OLD

];

[vignesh_s]

thanks,but i need some more to modify,my out put is matchmg ,i attached 2 images ,no1 is what i got,no2 xsl image

[Anil_Babu_Samineni]

Can we have your excel file? It's very difficult with different text ..

[vignesh_s]

here is the xsl file

[Peter_Cammaert]

With all due respect, I think you failed to understand the question Or Shoham was asking.

QlikView is software, and in order to let it do something in a reliable way, we need to tell it how to do it. This usually boils down to defining and programming a set of rules for processing incoming data, as QlikView isn't Artificially Intelligent yet to deduce the rules by itself by observing examples.

These rules that we need directly lead to script statements. The more rules we need, the more complex the script code. Simple rules ("split here, stop overthere") lead to efficient and maintainable code. Because of the earlier statement "...there doesn't seem to be any consistent way to determine... '', we can probably forget about a small set of rules. Instead we may end up with a large set because every single example may need to be handled in a different way. But we don't know yet because we haven't seen them all. That's why - if you cannot describe the rules for splitting Product names into Category and Category type - we need a dump of all unique mappings to create a rule set and script code that is able to process them all, and not a simple copy of a screenshot in XLS format.

Unfortunately this path may lead to problems in the future. As soon as someone else introduces a new product name that we haven't encountered before (for example "A B C" where A is the Category and B C is the Category type) the new solution will almost certainly fail. If this processing is not single-shot to migrate away once-and-for-all from the weird product naming, then you'ld better ask your users to obey a few simple rules themselves to name a new product. That will make life a lot easier in the future...