topic Re: subfield,left in QlikView

subfield,left

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: subfield,left

Or — Mon, 25 Dec 2017 12:20:44 GMT

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.

Re: subfield,left

vignesh_s — Mon, 25 Dec 2017 12:25:12 GMT

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

Re: subfield,left

Anil_Babu_Samineni — Mon, 25 Dec 2017 12:37:58 GMT

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

Re: subfield,left

vignesh_s — Mon, 25 Dec 2017 12:39:44 GMT

Can i get the expression for this

Re: subfield,left

Anil_Babu_Samineni — Mon, 25 Dec 2017 12:42:21 GMT

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

];

Re: subfield,left

vignesh_s — Mon, 25 Dec 2017 14:44:42 GMT

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

Re: subfield,left

Anil_Babu_Samineni — Mon, 25 Dec 2017 16:56:19 GMT

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

Re: subfield,left

vignesh_s — Tue, 26 Dec 2017 07:39:16 GMT

here is the xsl file

Re: subfield,left

Peter_Cammaert — Tue, 26 Dec 2017 08:54:19 GMT

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

Re: subfield,left

Anil_Babu_Samineni — Tue, 26 Dec 2017 12:16:43 GMT

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?

Re: subfield,left

sunny_talwar — Tue, 26 Dec 2017 12:23:16 GMT

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.

Re: subfield,left

vignesh_s — Tue, 26 Dec 2017 12:30:10 GMT

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

Re: subfield,left

sunny_talwar — Tue, 26 Dec 2017 12:32:27 GMT

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?

Re: subfield,left

Or — Tue, 26 Dec 2017 12:39:29 GMT

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.

Re: subfield,left

vignesh_s — Tue, 26 Dec 2017 12:45:35 GMT

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

Re: subfield,left

sunny_talwar — Tue, 26 Dec 2017 13:00:24 GMT

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

Re: subfield,left

vignesh_s — Tue, 26 Dec 2017 13:54:08 GMT

thanks a lot

Re: subfield,left

Anil_Babu_Samineni — Wed, 27 Dec 2017 06:12:01 GMT

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

Re: subfield,left

sunny_talwar — Wed, 27 Dec 2017 12:24:10 GMT

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

QlikView Addict: Removing Leading Zeros