Need to Separate a String In to multiple fields having multiple delimiters

[VishnuKR445]

Hi 

I am new to Qlik Sense

I need to split a field  Product  into Category and Subcategory

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
Edelweiss Tokio Life - Wealth Accumulation (Privilege)	edelweiss tokio life	WEALTH ACCUMULATION
Edelweiss Tokio Life Education (Term-Regular Pay/5pay/7 pay/10pay/15 pay)	edelweiss tokio life	EDUCATION
Edelweiss Tokio Life Group Credit Protection - ECL	edelweiss tokio life	GROUP CREDIT PROTECTION
Edelweiss Tokio Life Group Credit Protection - ECL - NEW	edelweiss tokio life	GROUP CREDIT PROTECTION
Edelweiss Tokio Life Group Credit Protection - EHF	edelweiss tokio life	GROUP CREDIT PROTECTION
Edelweiss Tokio Life Group Credit Protection - EHF - NEW	edelweiss tokio life	GROUP CREDIT PROTECTION
Edelweiss Tokio Life Group Credit Protection - EHF (LAP)	edelweiss tokio life	GROUP CREDIT PROTECTION
Edelweiss Tokio Life Group Credit Protection with ATPD - ECL	edelweiss tokio life	GROUP CREDIT PROTECTION WITH ATPD
Edelweiss Tokio Life Group Credit Protection with ATPD - EHF	edelweiss tokio life	GROUP CREDIT PROTECTION WITH ATPD
Edelweiss Tokio Life Group Credit Protection with ATPD - SME	edelweiss tokio life	GROUP CREDIT PROTECTION WITH ATPD
Edelweiss Tokio Life Income Replacement Plan - 5pay/10pay	edelweiss tokio life	INCOME REPLACEMENT PLAN
Edelweiss Tokio Life Income Replacement Plan - Reg/5pay/10pay - OLD	edelweiss tokio life	INCOME REPLACEMENT PLAN
Edelweiss Tokio Life MultiGain Plan (WA)	edelweiss tokio life	MULTIGAIN PLAN
Edelweiss Tokio Life Safe n Sure Plan (WA)	edelweiss tokio life	SAFE N SURE PLAN
Edelweiss Tokio Life Safe n Sure Plan (WA) - OLD	edelweiss tokio life	SAFE N SURE PLAN
Edelweiss Tokio Life Save n Grow Plan (WA)	edelweiss tokio life	SAVE N GROW PLAN
Edelweiss Tokio Life Wealth Accumulation (Accelerated cover)	edelweiss tokio life	WEALTH ACCUMULATION
Edelweiss Tokio Life Wealth Accumulation (Comprehensive Cover)	edelweiss tokio life	WEALTH ACCUMULATION
Edelweiss Tokio Life Wealth Accumulation (Cover Plus)	edelweiss tokio life	WEALTH ACCUMULATION
Edelweiss Tokio Life Wealth Enhancement Ace	edelweiss tokio life	WEALTH ENHANCEMENT ACE

I need to load only product field and split the product field to other two field but there are different types of delimiter which will comes to play while splitting as the subfield will split based on only one delimiter 


i was able to get category using :- Left(Trim(Product),Index(Product,' ',3)-1)as Category 

bit i am not able to achieve subcategory field 

can you help me achieve this scenario 

[marcus_sommer]

If you have just a few delimiters and/or variants you could use some (nested) if-loops to check them and then to branch to the appropriate extraction which may not just be left/right/mid else also subfield() or textbetween(). It won't be nice but possible. If there are some more combinations such an approach becomes quite ugly - therefore I suggest to consider to change the approach to a Mapping ‒ Qlik Sense on Windows.

- Marcus

[edwin]

if the different delimiters have individual meanings then Marcus' solution is the best.  however, if the delimiters dont have any meaning - that you treat them the same then maybe youd want to replace the different delimeters first before you parse them.  you can use the replace function. 

[VishnuKR445]

i tried using nested if's but im not able to get appropriate result i used the following logic 

if(WildMatch(Product,'*-*')>0,Left(Trim(Product),Index(Product,'-')-1),(If(WildMatch(Product,'*(*')>0,Left(Trim(Product),Index(Product,'(')-1),Product))) as sub_Category,

[marcus_sommer · Accepted solution]

Only two queries and extracts on the content won't be enough - you will probably need some more and also including/combining the queries with len() and substringcount() and/or checking the sub-strings more specific and/or against already existing ones. Important will be that the queries are unique and within the right order to avoid that a lower complexity is true before a more complex check is made.

Like above mentioned it's definitely not a funny task and therefore I suggest to check the mapping-approach again.

- Marcus