How to split field value as per our my requirement

[Naps_230]

Hi ,

Can you please help me , how to split field value as per our my requirement, Please find the attachment.

we multiple records in excels. I need category and subcategory field like below, please refer attachment

MY Requirement below:

Product	Category	Subcategory
Edelweiss Tokio Group Credit Protection - SME	edelweiss tokio group	CREDIT PROTECTION

[MarcoWedel]

How is the logic of identifying Category and Subcategory specified?

[Naps_230]

Hi ,

we need to create two field like product and subcategory based on product.

[Naps_230]

Hi Marco ,

we need to create two field like product and subcategory based on product field.

[MarcoWedel]

Hi,

one solution that implements your requirement to create two field like product and subcategory based on product:

Your Requirement below:

Product	Category	Subcategory
Edelweiss Tokio Group Credit Protection - SME	edelweiss tokio group	CREDIT PROTECTION

solution:

table1:
Load *,
Lower(Left(Product,21)) as Category,
Upper(Mid(Product,23,17)) as Subcategory
Inline [
Product
Edelweiss Tokio Group Credit Protection - SME
];

hope this helps

please close your thread by accepting a solution

thank you

[Naps_230]

Hi Marco,

Thanks for reply, Actually i have multiple row in file. how will it work if data looks like. i have attached screenshot of my requirement.

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/pay)	edelweiss tokio life	PROTECTION
Edelweiss Tokio Life - Single Pay Endowment Assuarance Plan	edelweiss tokio life	SINGLE PAY ENDOWMENT ASSURANCE PLAN

[Sujy15]

Hi @Naps_230 ,

Please find the expressions as below :

For Category : SubField(Product,'-',1)

For Subcategory : If(SubStringCount(Product,'-')=1,SubField(Product,'-',-1),TextBetween(Product,'-','-',1))

P.S : I have tried the solution by adding '-' for the first two rows in Product field (as highlighted in the above image)

Hope this helps you!

Thanks!

[MarcoWedel]

Coming back to my earlier question of how the product has to be split in category and subcategory, like in general.

If you only were given the products and asked to split them manually, what portion of the product string would you put in the category field and what in the subcategory based on a general rule rather than a few examples?

If you can answer this question, then there will surely be an implementation in Qlik.