topic Re: Seperating out Field in QlikView

Seperating out Field

Wed, 25 Nov 2020 16:16:04 GMT

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

Field1	Field2
Banana	East
15487	North

Re: Seperating out Field

ciaran_mcgowan — Wed, 06 Jul 2016 15:30:37 GMT

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

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

Re: Seperating out Field

Wed, 06 Jul 2016 15:36:46 GMT

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

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 15:48:11 GMT

Is there a list of possible characters?

Re: Seperating out Field

Wed, 06 Jul 2016 15:51:41 GMT

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

% $ & _ etc.

There are others. Mostly special characters.

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 15:58:18 GMT

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

];

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 (|)

Re: Seperating out Field

ciaran_mcgowan — Wed, 06 Jul 2016 15:59:00 GMT

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.

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 16:00:56 GMT

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

Re: Seperating out Field

ciaran_mcgowan — Wed, 06 Jul 2016 16:06:08 GMT

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.

Re: Seperating out Field

antoniotiman — Wed, 06 Jul 2016 16:06:47 GMT

Try

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

Re: Seperating out Field

ciaran_mcgowan — Wed, 06 Jul 2016 16:12:18 GMT

The RIGHT function here won't return the correct values. You need to use LEN(Field)-FINDONEOF(.....

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 16:12:46 GMT

This is good, but somehow feel that Mapping Load might be slightly easier to maintain in the long run as we can maintain this in an Excel file rather then listing them out here and missing something by chance

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 16:14:37 GMT

qlik_kev‌-

Did you get what you were looking for? If you did, please close this thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Thanks and regards,

Sunny

Re: Seperating out Field

Wed, 06 Jul 2016 16:16:25 GMT

Thanks Sunny T but this isn't something I'd like to maintain in an external excel file so I'm going to go with the FindOneOf approach.

Re: Seperating out Field

sunny_talwar — Wed, 06 Jul 2016 16:19:29 GMT

External Excel file was just an option. But feel free to go with the FindOneOf approach . Please mark ciaran.mcgowan response as correct answer as close this thread.

Best,

Sunny