Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Seperating out Field

Is there a way to separate fields that have been accidently combined in a source document?

Example: Banana-East or 15487-North

I need them to read

Field1Field2
BananaEast
15487North


1 Solution

Accepted Solutions
Highlighted
Partner
Partner

True. He could also try something like this:

Left(Example, FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)-1) AS Field 1

Right(Example, Len(Example)-FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)) AS Field 2

No need for Mapping Load and can put all special characters between 2 single quotes.

View solution in original post

14 Replies
Highlighted
Partner
Partner

Subfield(Example, '-', 1) AS Field1

Subfield(Example, '-', 2) AS Field2

Highlighted
Not applicable

Thank you but that only works for fields separated by a dash ('-'), is there a way to do this for other characters as well?

Highlighted

Is there a list of possible characters?

Highlighted
Not applicable

From what I can see it can be a space or one of these:

% $ & _ etc.

There are others. Mostly special characters.

Highlighted

May be try with a Mapping Load:

MappingTable:

Mapping

LOAD * Inline [

SpecialChar, Default

%, |

$, |

&, |

_, |

" " , |

-, |

];

Table:

LOAD Subfield(Fieldname, '|', 1) AS Field1,

  Subfield(Fieldname, '|', 2) AS Field2;

LOAD MapSubString('MappingTable', FieldName) as Fieldname;

LOAD * Inline [

FieldName

Banana-East

Banana&West

15487$North

1548_South

1010 North

];


Capture.PNG


UPDATE:
Essentially add all possible special characters to your mapping load. This will convert all of them into pipe (or you can use any other one you would like) and then use SubField with a pipe (|)

Highlighted
Partner
Partner

If you know all the characters you could still use SUBFIELD by replace all these characters to a single one.

Subfield(

    Replace(Replace(Replace(Replace(Replace(Example,'%','-'),'$','-'),'&','-'),'_','-'),' ','-')

, '-', 1) AS Field1

Subfield(

    Replace(Replace(Replace(Replace(Replace(Example,'%','-'),'$','-'),'&','-'),'_','-'),' ','-')

, '-', 2) AS Field2



This will replace all your special characters to '-' and then applies my original SUBFIELD function to it. You can add as many REPLACE functions as you need.

Highlighted

Very difficult to manage this ciaran.mcgowan‌, I would rather use a mapping load here

Highlighted
Partner
Partner

True. He could also try something like this:

Left(Example, FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)-1) AS Field 1

Right(Example, Len(Example)-FindOneOf(Example, ' !"£$%^&*()_+-={}[]:;@~#<>,./\|?¬', 1)) AS Field 2

No need for Mapping Load and can put all special characters between 2 single quotes.

View solution in original post

Highlighted
Master III
Master III

Try

Left('A11&B22',FindOneOf('A11&B22','-_$%&/()=')-1)
Right('A11&B22',FindOneOf('A11&B22','-_$%&/()=')-1)