Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Category | SubCategory |
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 |
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;
Is there a consistent way to know where the break needs to be? or is this completely random?
the output should resemble like this i have shown below
Category | SubCategory |
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 |
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.
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;
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
Category | SubCategory |
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 |
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
Thanks for your help robert99 i just found of solution for this