Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

how to seperate data from one cells into multiple cell

hi i am new to qlik how to seperate values from data from one cell into multiple cells please help me  

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
Edelweiss Tokio Life - Wealth Accumulation (Privilege)
Edelweiss Tokio Life Education (Term-Regular Pay/5pay/7 pay/10pay/15 pay)
Edelweiss Tokio Life Group Credit Protection - ECL
Edelweiss Tokio Life Group Credit Protection - ECL - NEW
  Edelweiss Tokio Life Group Credit Protection - EHF
  Edelweiss Tokio Life Group Credit Protection - EHF - NEW
Edelweiss Tokio Life Group Credit Protection - EHF (LAP)
Edelweiss Tokio Life Group Credit Protection with ATPD - ECL
Edelweiss Tokio Life Group Credit Protection with ATPD - EHF
Edelweiss Tokio Life Group Credit Protection with ATPD - SME
Edelweiss Tokio Life Income Replacement Plan - 5pay/10pay
Edelweiss Tokio Life Income Replacement Plan - Reg/5pay/10pay - OLD
Edelweiss Tokio Life MultiGain Plan (WA)
Edelweiss Tokio Life Safe n Sure Plan (WA)
  Edelweiss Tokio Life Safe n Sure Plan (WA) - OLD
Edelweiss Tokio Life Save n Grow Plan (WA)
Edelweiss Tokio Life Wealth Accumulation (Accelerated cover)
  Edelweiss Tokio Life Wealth Accumulation (Comprehensive Cover)
Edelweiss Tokio Life Wealth Accumulation (Cover Plus)
Edelweiss Tokio Life Wealth Enhancement Ace

and my output should be

  

CategorySubCategory
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio life CASH FLOW PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
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 WEALTH ACCUMULATION
edelweiss tokio life EDUCATION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life MULTIGAIN PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAVE N GROW PLAN
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ENHANCEMENT ACE
1 Solution

Accepted Solutions
rrsrini2907
Contributor

Re: how to seperate data from one cells into multiple cell

Hi Naveen,

If you execute the below script, you would get the desired result.

map1:

mapping load * inline [

x, y

Edelweiss Tokio Group, 

Edelweiss Tokio Life -,

] ;

map2:

mapping load * inline [

x, y

Edelweiss Tokio Group, 1;

Edelweiss Tokio Life -, 2;

] ;

Map3:

Mapping LOAD * Inline [

x, y

1, Edelweiss Tokio Group

2, Edelweiss Tokio Life

] ;

LOAD *,

  Upper(Trim(Temp3SubCategory)) as SubCategory;

LOAD *,

  Left (TempSubCategory,

  IF(Temp2SubCategory = 0, Len(TempSubCategory), Temp2SubCategory-1)) as Temp3SubCategory;

LOAD *,

  FindOneOf(TempSubCategory, '-') as Temp2SubCategory;

LOAD *,

  ApplyMap('Map3', Temp3Category) as Category;

LOAD *,

  Trim(Left(TempCategory, Temp2Category)) as Temp3Category;

LOAD *,

  FindOneOf(TempCategory, ';')-1 as Temp2Category;

LOAD Trim(MapSubstring ('map2', Product)) as TempCategory,

  Trim(MapSubstring ('map1', Product)) as TempSubCategory,

  Product;

LOAD * 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)

];

Drop Fields TempCategory, Temp2Category, Temp3Category, TempSubCategory, Temp2SubCategory, Temp3SubCategory;

7 Replies
MVP
MVP

Re: how to seperate data from one cells into multiple cell

Is there a consistent way to know where the break needs to be? or is this completely random?

Not applicable

Re: how to seperate data from one cells into multiple cell

the output should resemble like this i have shown below

   

CategorySubCategory
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio life CASH FLOW PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
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 WEALTH ACCUMULATION
edelweiss tokio life EDUCATION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life MULTIGAIN PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAVE N GROW PLAN
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ENHANCEMENT ACE
rrsrini2907
Contributor

Re: how to seperate data from one cells into multiple cell

Hi Naveen,

In order to achieve this, you have to use the combination of MapSubstring, FindOneOf, Left.

Also you have to create two mapping tables.

Regards,

Srini.

rrsrini2907
Contributor

Re: how to seperate data from one cells into multiple cell

Hi Naveen,

If you execute the below script, you would get the desired result.

map1:

mapping load * inline [

x, y

Edelweiss Tokio Group, 

Edelweiss Tokio Life -,

] ;

map2:

mapping load * inline [

x, y

Edelweiss Tokio Group, 1;

Edelweiss Tokio Life -, 2;

] ;

Map3:

Mapping LOAD * Inline [

x, y

1, Edelweiss Tokio Group

2, Edelweiss Tokio Life

] ;

LOAD *,

  Upper(Trim(Temp3SubCategory)) as SubCategory;

LOAD *,

  Left (TempSubCategory,

  IF(Temp2SubCategory = 0, Len(TempSubCategory), Temp2SubCategory-1)) as Temp3SubCategory;

LOAD *,

  FindOneOf(TempSubCategory, '-') as Temp2SubCategory;

LOAD *,

  ApplyMap('Map3', Temp3Category) as Category;

LOAD *,

  Trim(Left(TempCategory, Temp2Category)) as Temp3Category;

LOAD *,

  FindOneOf(TempCategory, ';')-1 as Temp2Category;

LOAD Trim(MapSubstring ('map2', Product)) as TempCategory,

  Trim(MapSubstring ('map1', Product)) as TempSubCategory,

  Product;

LOAD * 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)

];

Drop Fields TempCategory, Temp2Category, Temp3Category, TempSubCategory, Temp2SubCategory, Temp3SubCategory;

Not applicable

Re: how to seperate data from one cells into multiple cell

What u siad is correct but it doesnot apply to many to datas as my data like like this

 

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
Edelweiss Tokio Life - Wealth Accumulation (Privilege)
Edelweiss Tokio Life Education (Term-Regular Pay/5pay/7 pay/10pay/15 pay)
Edelweiss Tokio Life Group Credit Protection - ECL
Edelweiss Tokio Life Group Credit Protection - ECL - NEW
  Edelweiss Tokio Life Group Credit Protection - EHF
  Edelweiss Tokio Life Group Credit Protection - EHF - NEW
Edelweiss Tokio Life Group Credit Protection - EHF (LAP)
Edelweiss Tokio Life Group Credit Protection with ATPD - ECL
Edelweiss Tokio Life Group Credit Protection with ATPD - EHF
Edelweiss Tokio Life Group Credit Protection with ATPD - SME
Edelweiss Tokio Life Income Replacement Plan - 5pay/10pay
Edelweiss Tokio Life Income Replacement Plan - Reg/5pay/10pay - OLD
Edelweiss Tokio Life MultiGain Plan (WA)
Edelweiss Tokio Life Safe n Sure Plan (WA)
  Edelweiss Tokio Life Safe n Sure Plan (WA) - OLD
Edelweiss Tokio Life Save n Grow Plan (WA)
Edelweiss Tokio Life Wealth Accumulation (Accelerated cover)
  Edelweiss Tokio Life Wealth Accumulation (Comprehensive Cover)
Edelweiss Tokio Life Wealth Accumulation (Cover Plus)
Edelweiss Tokio Life Wealth Enhancement Ace

And output should be like this

  

CategorySubCategory
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio group CREDIT PROTECTION
edelweiss tokio life CASH FLOW PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
edelweiss tokio life PROTECTION
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 WEALTH ACCUMULATION
edelweiss tokio life EDUCATION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life GROUP CREDIT PROTECTION WITH ATPD
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life INCOME REPLACEMENT PLAN
edelweiss tokio life MULTIGAIN PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAFE N SURE PLAN
edelweiss tokio life SAVE N GROW PLAN
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ACCUMULATION
edelweiss tokio life WEALTH ENHANCEMENT ACE
robert99
Valued Contributor II

Re: how to seperate data from one cells into multiple cell

it could be tricky to do this

But to get the first column if its always three names. One way is to use subfield and &

But there doesn't seem to be any clear logically pattern to get the second field. This takes time. I have used purgechar, substringcount,  len etc to do this at times

Not applicable

Re: how to seperate data from one cells into multiple cell

Thanks for your help robert99  i just found of solution for this