Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Extracting substring from record

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.

1 Solution

Accepted Solutions
Highlighted

Re: Extracting substring from record

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

View solution in original post

7 Replies
Highlighted
Not applicable

Re: Extracting substring from record

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

Highlighted

Re: Extracting substring from record

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

View solution in original post

Highlighted

Re: Extracting substring from record

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

Peter

Highlighted
MVP
MVP

Re: Extracting substring from record

Try like:

Load

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

Highlighted

Re: Extracting substring from record

You could try

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

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

Highlighted
Not applicable

Re: Extracting substring from record

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.

Highlighted
Not applicable

Re: Extracting substring from record

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