topic Re: Calling dynamically a MapSubString table in App Development

Calling dynamically a MapSubString table

kcollorig — Sat, 16 Nov 2024 18:00:55 GMT

Hello all,

I'm trying to call the MapSubString function dynamically within a load statement but it is not working...

Here is an example :

Mapsubstring_Table1: MAPPING LOAD * INLINE [ From, To Value_1, Value_2 ]; Mapsubstring_Table2: MAPPING LOAD * INLINE [ From, To Value_3, Value_4 ]; Table_Raw: LOAD * INLINE [ Value, MappingTable Value1, Table1 Value3, Table2 ]; Table_Mapped: LOAD *, MapSubString('Mapsubstring_' & MappingTable, Value) AS Value_Mapped RESIDENT Table_Raw;

The dynamic call of the Mapping tables thanks to the MappingTable field does not work... I have the error 'MapSubString function takes a constant map as first parameter'.

Please note that above is just a simple example and that in my case I need to have multiple mapping tables... Any idea ?

Re: Calling dynamically a MapSubString table

sunny_talwar — Mon, 05 Oct 2020 18:18:46 GMT

@kcollorig I am not sure if it will work like this, you might have to use an if statement or pick match to have multiple mapsubstrings

Re: Calling dynamically a MapSubString table

kcollorig — Mon, 05 Oct 2020 20:38:37 GMT

Thanks for your answer! Unfortunately I have too many mapping tables to do this...

In fact what I'm trying to do is to make a substring map depending on the value of two other fields. Maybe you would have another idea on how to do this?

Re: Calling dynamically a MapSubString table

Kushal_Chawda — Mon, 05 Oct 2020 21:46:38 GMT

@kcollorig You can map single field value using multiple mapping table. Also you can map multiple fields using multiple mapping tables but you need to write multiple mapsubstring function.

If your mapping tables are having same field name then they will autoconcatenate and then you can use single mapsubsrting statement if you want to map single field or you can use multiple mapsubstring.

Mapsubstring_Table:
MAPPING LOAD * INLINE [
From, To
Value_1, Value_2
];

MAPPING LOAD * INLINE [
From, To
Value_3, Value_4
];

MAPPING LOAD * INLINE [
From, To
Other_1, Other_2
];

Table_Raw:
LOAD *,MapSubString('Mapsubstring_Table', Value) as Map_Value,
MapSubString('Mapsubstring_Table', Value2) as Map_Value2
INLINE [
Value, MappingTable, Value2
Value_1, Table1, Other_1
Value_3, Table2, Other
];

Re: Calling dynamically a MapSubString table

kcollorig — Tue, 06 Oct 2020 08:27:12 GMT

Hi Kush, thanks for your answer. In fact I'm in a particular case and the example I gave do not represent it correctly... So I'll try to explain in details what I'm trying to achieve.

My data come from forms with free text areas as answers. So since the answers are not standard, I need to identify some words in it. For that, I created a vocabulary reference excel file that gives for each TYPE of form and each QUESTION, the words that I'm looking in the ANSWER.

For example I have these data :

Form_Answers: LOAD * INLINE [ Form_Type, Form_Question, Form_Answer Type_1, Oject Lost ?, I didn't remember where I put my phone Type_1, Oject Lost ?, I've lost my computer and phone Type_2, Computer Brand ?, The brand of my Computer is HP ];

And what I want to do is this :

[Mapsubstring_Type_1_Object_Lost_?]: MAPPING LOAD * INLINE [ From, To computer, --> Computer <-- phone, --> Phone <-- tablet, --> Tablet <-- ]; [Mapsubstring_Type_2_Computer_Brand_?]: MAPPING LOAD * INLINE [ Toshiba, --> Toshiba <-- HP, --> HP <-- Asus, --> Asus <-- ]; Form_Answers: LOAD * INLINE [ Form_Type, Form_Question, Form_Answer Type_1, Oject_Lost_?, I didn't remember where I put my phone Type_1, Oject_Lost_?, I've lost my computer and phone Type_2, Computer_Brand_?, The brand of my Computer is HP ]; Form_Answers_Mapped: LOAD Distinct Form_Type, Form_Question, TextBetween(Form_Answer_Mapped, '-->', '<--', IterNo()) AS Form_Answer_Standard WHILE Not Isnull(TextBetween(Form_Answer_Mapped, '-->', '<--', IterNo())) ; LOAD *, MapSubString('Mapsubstring_' & Form_Type & Form_Question, Form_Answer) AS Form_Answer_Mapped RESIDENT Form_Answers;

Please note that I have a lot of form types and questions and that unlike the example above all my mapping tables are created dynamically.

Maybe my approach is not the good one... If you have any idea ?

Re: Calling dynamically a MapSubString table

marcus_sommer — Tue, 06 Oct 2020 09:26:30 GMT

I think I would rather try to use just a single mapping - means at first concatenating all mapstring-areas into one table by differentiating their return-values, like:

[MapsubstringPrepare]:
LOAD * INLINE [
From, To
computer, --> Computer <--
phone, --> Phone <--
tablet, --> Tablet <--
];

concatenate([MapsubstringPrepare])
LOAD * INLINE [
From, To
Toshiba, -------> Toshiba <-------
HP, -------> HP <-------
Asus, -------> Asus <-------
];

With it you could fetch accurate those values per textbetween() you want. You mentioned that all the tables are dynamically created - I assume in some kind of loop, if yes, you could use the loop-counter to set the needed extra chars for the return values - maybe something like:

repeat(chr(1), [LOOPCOUNTER] /* $(LOOPCOUNTER)*/)

Beside this I assume that you will also need some cleaning and structuring of the free-text. This means including/excluding of certain chars with keepchar/purgechar(), for example any line-breaks, quotes, maybe numbers, chars above chr(255), multiple spaces ...

Further removing filler words like and, or, do, did, have, also, too ... and also separating adjective, verbs, nouns and so on - to classify the content (maybe also with mapsubstring). You may need this to get more context to the answers and maybe it leads into any kind of a word-cloud.

- Marcus

Re: Calling dynamically a MapSubString table

kcollorig — Wed, 04 Nov 2020 15:28:29 GMT

Thanks Marcus! I made something like this and it worked.

Thanks to all for your responses also !