topic Re: Loop on applymap() within a load in App Development

Loop on applymap() within a load

Amiran — Tue, 17 Nov 2020 15:01:29 GMT

Hi,

I am creating a multi-language dashboard and the translation data comes from the DB so I need to create a dynamic load in case more languages will be added (and because there is a lot of languages and fields to translate),

I am trying to create a load script that contains a loop to go over the applymap() and change the fields within the function,

for example:

PLOTS:
LOAD
SECTION_ACCESS_REDUCTION,
SOURCE_DB,
PROJECT_ID,
PROJECT_LOCALE_MAPPER,
LOCATION_ID,

//---- Apply map() -------------------------
PROJECT_NAME,
ApplyMap('PROJECT_NAME_en',NAME) AS PROJECT_NAME_en,
ApplyMap('PROJECT_NAME_es',NAME) AS PROJECT_NAME_es,
ApplyMap('PROJECT_NAME_fr',NAME) AS PROJECT_NAME_fr,
ApplyMap('PROJECT_NAME_he',NAME) AS PROJECT_NAME_he,
...and more....
NAME,
ApplyMap('NAME_en',NAME) AS NAME_en,
ApplyMap('NAME_es',NAME) AS NAME_es,
ApplyMap('NAME_fr',NAME) AS NAME_fr,
ApplyMap('NAME_he',NAME) AS NAME_he,
......and more.......
CROP_NAME,
ApplyMap('CROP_NAME_en',CROP_NAME) AS CROP_NAME_en,
ApplyMap('CROP_NAME_es',CROP_NAME) AS CROP_NAME_es,
ApplyMap('CROP_NAME_fr',CROP_NAME) AS CROP_NAME_fr,
ApplyMap('CROP_NAME_he',CROP_NAME) AS CROP_NAME_he,
.......and more......

FROM $(vQVD_ERD_path)PLOTS.qvd (qvd);

I thought about trying to create a script with a loop that will change the data within the apply map function:

PLOTS:
LOAD
SECTION_ACCESS_REDUCTION,
SOURCE_DB,
PROJECT_ID,
PROJECT_LOCALE_MAPPER,
LOCATION_ID,

//---- Apply map() -------------------------
PROJECT_NAME,

NAME,

CROP_NAME,

For Row_num_APP_FILEDS_NAMES = 0 to vCount_SOURCE_DB-1

let vFILEDS_NAMES=Peek('FILEDS_NAMES' ,[Row_num_APP_FILEDS_NAMES], [APP_FILEDS_NAMES]);

for Row_num = 0 to vCount_Langugagess-1

let lang_name=Peek('language_name',[Row_num_language_name], 'Temp_language_list');
Let vTableName = '$(vFILEDS_NAMES)' & Chr(95) & '$(lang_name)';

ApplyMap('$(vTableName)',$(vFILEDS_NAMES)) AS $(vTableName)

FROM $(vQVD_ERD_path)PLOTS.qvd (qvd);

how can I create a Load script that has a loop within the script and go over the applymap function without hardcoded the data for each filed and language?

Re: Loop on applymap() within a load

chrismarlow — Tue, 17 Nov 2020 17:14:10 GMT

Hi,

I would probably not go with apply map in the loop, rather I would just join (I am assuming that the source of translations can be guaranteed not to contain duplicates, that would be a reason to use apply map).

From there the looping would look something like the toy application attached - I've not tried to match exactly to your field/table names - but hopefully gives an idea.

I don't know if there is any guidance on other ways of 'localising' Qlik, as I think you might end up with an over complex application here.

Cheers,

Chris.

Re: Loop on applymap() within a load

QFabian — Tue, 17 Nov 2020 17:51:18 GMT

Hi, In a Qlik project you can create variables, and then use them in objects titles or texts,

So you first load your dictionary of terms, then create the variables to use as titles in objects, and then have a field for select the language.

i fund this, i hope it helps you

http://www.qlikfix.com/2016/10/04/handling-multiple-languages-and-translations/

Re: Loop on applymap() within a load

Amiran — Wed, 18 Nov 2020 07:54:13 GMT

Hi Fabian,
thank you for your reply,

I am following a different document but it looks like the same logic,
so far I am able to translate the data but because we have lots of languages and fields to translate I don't want to hardcoded applymap() to every field and language and it also may vary,
that why I want to create a loop within a Load statement that dos it.

Re: Loop on applymap() within a load

Amiran — Wed, 18 Nov 2020 08:07:34 GMT

Hi Chris,

thank you,

I am following documentation about how to create a multi language app,

If I understand you correctly you were "flatten" the data and create a long table that holds all the languages and translations as columns.

how can I use it in the app without applymap() and translate the data?

it's supposed to work as filters?

I attached the document I follow along (scenario 2 in the doc).

https://community.qlik.com/t5/Qlik-Design-Blog/Handling-Multiple-Languages/ba-p/1473518#comments

Re: Loop on applymap() within a load

marcus_sommer — Wed, 18 Nov 2020 14:50:02 GMT

For me it looked as if you want to create the various translation fields within the fact-table. Personally I would tend to do this within the dimension-tables and I wouldn't try it with applymap() else rather joining the various translation-fields to the dimension-tables. For this you may use a loop-approach which looped through all available / needed languages. Should this loop really don't possible you could duplicate the fields which should be translated (for this you would need a variable created on the load-outside which returned the appropriate fields and their multiple renaming). Later the real translation is done with a Map ... using ... approach, see:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptRegularStatements/Map.htm

More easier as the above could be to concatenate all of the translations of a dimension-table into a single table by differentiating the various parts with an extra flag-field for the languages and to use them as The-As-Of-Table.

- Marcus