topic Re: Dynamic ApplyMap in Connectivity & Data Prep

Dynamic ApplyMap

Vladislav_qv_dev — Fri, 15 Oct 2021 12:00:36 GMT

Hello, everyone!

I have a trouble with ApplyMap function in a script. There are several map tables, created in the beginning of the script. For example, 'apple fruit', 'orange fruit', 'banana fruit', etc.

In another table I need to use these different map tables depending on a field's value. I.e. I'm trying to use

LOAD *,

ApplyMap(Fruitname&' fruit', somefield) As new_field

Resident tmp;

Here Fruitname is a table's field, that contains values like 'apple', 'orange ', 'banana ', etc

But QV's raising an error 'map_id' not found. It seems, QV doesn't permit to calculate expressions inside ApplyMap function. Does anyone know, how to solve the problem or to find a workaround?

Re: Dynamic ApplyMap

stevejoyce — Fri, 15 Oct 2021 12:23:56 GMT

What if you concatenate your mapping tables into a single map table and have your (1st) key field a composite key. In your apply map, your first parameter (map_id) can be static with single quotes, but your 2nd parameter (expr) can have the same logic you used in your composite key.

Re: Dynamic ApplyMap

marcus_sommer — Fri, 15 Oct 2021 13:33:06 GMT

Recently I had trouble with the same issue and after a while playing this and that I decided to do exactly what Steve suggests. I'm not absolutely sure about the reason but I could imagine that the exists of the mapping-table is already checked during the table-initialization like the comparison between listed and existing fields. And on this time there isn't any evaluation of the field-value + string-value and therefore it failed.

- Marcus

Re: Dynamic ApplyMap

brunodec — Fri, 15 Oct 2021 14:53:29 GMT

Perhaps not the most elegant nor performant solution but I think technically this could do the job:

Apple_Fruit:
Mapping
Load * Inline [
Name, Color
Apple, Green
];

Banana_Fruit:
Mapping
Load * Inline [
Name, Color
Banana, Yellow
];

Orange_Fruit:
Mapping
Load * Inline [
Name, Color
Orange, Yellow
];

Data:
Load * Inline [
FRUITNAME
Apple
Banana
Orange
];

let vRows = NoOfRows('Data');

For i = 1 to $(vRows)

let vFruitName = peek('FRUITNAME', $(i)-1, 'Data');
let vMappingTable = '$(vFruitName)' & '_Fruit';

NewTable:
Load Peek('FRUITNAME', $(i)-1, 'Data') as FRUITNAME,
Applymap('$(vMappingTable)', '$(vFruitName)') as NewField
AutoGenerate 1;

NEXT i

Re: Dynamic ApplyMap

Vladislav_qv_dev — Wed, 27 Oct 2021 19:44:24 GMT

Thank you all for your replies! Eventually I've come to the solution to concatenate some of mapping tables and to use a composite key as the second parameter in the ApplyMap Function.