topic Re: Extracting substring from record in QlikView

Extracting substring from record

Mon, 21 Oct 2013 06:37:41 GMT

Hello community,

I ran into an issue that I need help with.

I have a categories column that displays text in German and English as one string. Instead of showing both languages I would like to use a language switch to only display one language at a time.

The field records look like this:

"01 Text in German / Text in English"

"02 Text in German / Text in English"

I would like to show either 01 Text in German or 01 Text in English

I already use a Translation-Table coming from Excel but only for column names. And here I already have the names but I don't know how I could switch a field record based on language selection.

My only guess is to create another column that is shown or hidden based on the language selection.

How can I extract the languages in above format?

I can use subfield(categories, '/') but then I loose the numbering in the front.

Thanks for your help.

Re: Extracting substring from record

Mon, 21 Oct 2013 06:51:31 GMT

Hi,

Can you upload some of the possible values of the Categories Column? So that we can find some pattern to achieve your goal.

If all the possible values are in your above given example format then you can use like below

Left(Categories,2) & SubField(Mid(Categories,4),'/') as Category

Re: Extracting substring from record

Peter_Cammaert — Mon, 21 Oct 2013 07:17:32 GMT

This is one of those multi-language tricks. Follow these steps:

Split your language strings into two colums, e.g. instead of '01 Deutsch / English', make it into a column called DE that contains '01 Deutsch" and another column EN that contains '01 English'
Define a standalone table with a single field called LangSel that contains just the values 'EN' and 'DE'. Put this field on a sheet like you would do with a selector for a series of months, e.g. listbox without caption or frame and single line with two columns. IMPORTANT: enable Properties->General->Always One Selected Value
Define a variable called vTexts that is set to the expression '=LangSel'
Now create a listbox that shows values from expression '=[$(vTextst)]'. You can use ='Texts in ' & Only(LangSel) as Caption.

No click the different languages and see what happens in the listbox. You can apply the same trick to columns in straight tables or table boxes as well. See example below.

Peter

Re: Extracting substring from record

Peter_Cammaert — Mon, 21 Oct 2013 07:18:31 GMT

BTW the same example above contains the necessary text-splitting-code for your strings. Have a look at the script.

Peter

Re: Extracting substring from record

tresB — Mon, 21 Oct 2013 07:20:18 GMT

Try like:

Load

SubField(YourField, '/', 1) & ' Or ' & Letf(YourField,2) & ' ' & SubField(YourField, '/', 2) as NewField

Re: Extracting substring from record

CELAMBARASAN — Mon, 21 Oct 2013 08:21:46 GMT

You could try

Subfield(categories, '/', 1) for German

Subfield(categories, ' ', 1) & ' ' & Subfield(categories, '/', 2) for English

Re: Extracting substring from record

Mon, 21 Oct 2013 08:41:41 GMT

Hi,

You can try like below in your load script.

SubField(Field,' ', 1) as LanguageID,

Right( SubField(Text,'/', 1), Len(SubField(Text,'/', 1))- len(SubField(Text,' ', 1))) as GermanText,

SubField(Text,'/', 2) as EnglishText.

Re: Extracting substring from record

Mon, 21 Oct 2013 12:23:25 GMT

thank you everyone for your posts. Give me a day or two to play with those different possibilities.