7 Replies Latest reply: May 3, 2016 3:20 AM by Naveen N RSS

    how to seperate data from one cells into multiple cell

    Naveen N

      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
        • Re: how to seperate data from one cells into multiple cell
          Sunny Talwar

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

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

              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
                • Re: how to seperate data from one cells into multiple cell
                  Srinivasan Radhakrishnan

                  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;

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

                      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
                • Re: how to seperate data from one cells into multiple cell
                  Srinivasan Radhakrishnan

                  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.

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

                    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