Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

subfield,left

ProductCategorySubCategory
Edelweiss Tokio Group Credit Protection - SMEedelweiss tokio group CREDIT PROTECTION
Edelweiss Tokio Group Credit Protection - SME - NEWedelweiss tokio group CREDIT PROTECTION
Edelweiss Tokio Life - Cash Flow Protectionedelweiss 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) - OLDedelweiss tokio life PROTECTION
Edelweiss Tokio Life - Protection - (Single Pay)edelweiss tokio life PROTECTION
Edelweiss Tokio Life - Protection  - (Single Pay) - OLDedelweiss tokio life PROTECTION
Edelweiss Tokio Life - Save n Prosperedelweiss tokio life SAVE N PROSPER
Edelweiss Tokio Life - Single Pay Endowment Assurance Planedelweiss tokio life SINGLE PAY ENDOWMENT ASSURANCE PLAN
Edelweiss Tokio Life - Single Pay Endowment Assurance Plan - OLDedelweiss 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

1 Solution

Accepted Solutions
Highlighted

Re: subfield,left

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$);


Capture.PNG

View solution in original post

20 Replies
Highlighted
Specialist III
Specialist III

Re: subfield,left

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.

Highlighted
Creator
Creator

Re: subfield,left

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

Highlighted

Re: subfield,left

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

Capture.JPG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Re: subfield,left

Can i get the expression for this

Highlighted

Re: subfield,left

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

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Re: subfield,left

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

test1.png

test2.png

Highlighted

Re: subfield,left

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Re: subfield,left

here is the xsl file

Highlighted

Re: subfield,left

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...