Calling dynamically a MapSubString table

[kcollorig]

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 ?

[sunny_talwar]

@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

[kcollorig]

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? 

[Kushal_Chawda]

@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
];

[kcollorig]

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 ?

[marcus_sommer · Accepted solution]

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

[kcollorig]

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

Thanks to all for your responses also !