How delimiter the data with many combinations

[ramrongala]

Hi Qlik Experts,

I need to arrange  the data  with good format.please find  below 

original_Values means actual data.

Need this format -- after modify data.

original_Values	Need this format
1 - NO fhdfhjfhf	NO fhdfhjfhf
2 - fdfbfdfdf ABILITY	fdfbfdfdf ABILITY
2. hgdgdb KNOWLEDGE	hgdgdb KNOWLEDGE
3. hdshdsdhsd KNOWLEDGE	hdshdsdhsd KNOWLEDGE
2 hhjhj INVOLVED	hhjhj INVOLVED
3 uyhgvfdw	uyhgvfdw

I used purgeChar(), KeepChar(),Subfield() but it effected reaming data (Please find the attached excel data for more understanding ).

Please help me.

[Frank_Hartmann]

Maybe like this:

LOAD 
if(index(orginal_Values,'>')=1,orginal_Values,if(index(orginal_Values,'-')=3,SubField(orginal_Values,' - ',2),
if(index(orginal_Values,' ')=2 or index(orginal_Values,'.')=2,
PurgeChar(orginal_Values,'234. '),orginal_Values))) as Test,
orginal_Values,
Requried_Values
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

[albertovarela]

Hello there, 

As there is no consistent pattern I'd suggest you to use the Mapping Load and the ApplyMap function.

For more details look at: Mapping Load and ApplyMap

I hope this helps.

[ramrongala]

HI Frank,

Thank you for giving solution, But not full fill exactly.Please find below comments and output values.

Please help to solve the issue.

original_Values	Required_Values	After using your code	Comments
1 - NO fhdfhjfhf	NO fhdfhjfhf	NO fhdfhjfhf	working as expected
2 - fdfbfdfdf ABILITY	fdfbfdfdf ABILITY	fdfbfdfdf ABILITY
3 - hgh jjkl	hgh jjkl	hgh jjkl
4 - Suragaa gjjh	Suragaa gjjh	Suragaa gjjh
2. hgdgdb KNOWLEDGE	hgdgdb KNOWLEDGE	hgdgdbKNOWLEDGE	here combined into one single word(removed space) but need space between
3. hdshdsdhsd KNOWLEDGE	hdshhsd KNOWLEDGE	hdshdsdhsdKNOWLEDGE
4. hdffhufh KNOWLEDGE	hdffhufh KNOWLEDGE	hdffhufhKNOWLEDGE
5. uytwyewy KNOWLEDGE	uytyewy KNOWLEDGE	uytwyew KNOWLEDGE
1 hjuyt DEMAND	hjuyt DEMAND	1hjuytDEMAND	here also space need between 2 words and where starting with 1 that value including 1 and combined (like 1hjuytDEMAND)
2 hhjhj INVOLVED	hhjhj INVOLVED	hhjhj INVOLVED
3 uyhgvfdw	uyhgvfdw	uyhgvfdw
4 bfgffbananj	bfgffbananj	bfgffbananj
5 hgtf jhugggygyg	hgtf jhugggygyg	hgtf jhugggygyg

[ramrongala]

HI Frank,

I think below excel format is not good so i am attaching the excel format.

Please have a look my comments in excel(sheet 2 tab).