Applymap variable with a parameter

[bc1]

Hi everyone  

I am trying to create a variable which uses applymap with a parameter and then uses this to figure out which field name is used (shown below)

This variable works without the paramter:

let vDim2 = '[' & APPLYMAP('MAP_DYN_DIM2',1.01) & ']';

Variable with parameter:

let vDim2test = '[' & APPLYMAP('MAP_DYN_DIM2',$1) & ']';

LIVE_DATA:
LOAD
1.01 AS REPORT_ID,
$(vDim2) as DIM2,
$(vDim3('1.01')) as DIM2test
FROM [lib://QS_DATA/QVD/xyz.qvd]
(qvd);

When the above tries to create DIM2Test it does not bring back anything indicating that the parameter is not getting calculated.  Has anyone any idea as to why this is incorrect?

I cannot shre app due to security reasons.
Thanks

[Mark_Little]

Hi

Not something i have tried before, but my guess would be the syntax and the $ expansion as it is try to do before the variable and the parameter.

Maybe try 

$($(vDIM2)('1,01'))

[bc1]

Thanks - but unfortunately I get the same issue.  I will continue to have a play around with the syntax.

[bc1]

just bumping this to see if anyone has additional thoughts/ideas on this.  thanks everyone

[Chanty4u]

try this

Parameters:
LOAD * INLINE [
Parameter, Value
Report_ID, 1.01
];

LIVE_DATA:
LOAD
Parameter,
ApplyMap('MAP_DYN_DIM2', Value) as DIM2,
ApplyMap('MAP_DYN_DIM2', $(=FieldValue('Parameter', 1))) as DIM2test
RESIDENT Parameters
WHERE Parameter = 'Report_ID';

DROP TABLE Parameters;

[bc1]

that doesn't work either unfortunately.  essentially I want to be able to reuse my applymap variable multiple times with different parameters.

so e.g.when $1 = 1.01
it would load
REGION as DIM1

if i used 1.02
it would load
CONTINENT as DIM1

(this is just an example and it could be many, many dimensions.)

[marcus_sommer]

The syntax isn't quite correct because the applymap() is wrapped with square-brackets, the variable is created per LET and the variable-call queries a string and not a number. Therefore try it with:

SET vDim2test = "APPLYMAP('MAP_DYN_DIM2', $1, 'no Match')";

LIVE_DATA: LOAD
1.01 AS REPORT_ID,
$(vDim2test(1.01)) as DIM2test
FROM [lib://QS_DATA/QVD/xyz.qvd] (qvd);

[bc1]

Thanks for the help Marcus, 

This gets me nearly there and it loads successfully but not quite what is required....
I want the code to basically say : if 1.01 is mapped to the field REGION it should say:
REGION as DIM2test

but with the code above it is actually loading it like a string value
'REGION' as DIM2test

The reason for the square brackets were in case a field name had a space in the name.

[marcus_sommer · Accepted solution]

It's not possible to create a dynamic load-statement in this way because your variable-call returns always a string which won't be interpreted as field-reference. To get it a double dollar-sign expansion would be needed which isn't implemented in the script and also an evaluate() won't help.

This means you will need to create the dynamic part of the load-statement in before the load. In regard to your probably simplified description you might replace the mapping-approach with a normal table and then using lookup() within the variable-creation, like:

let vDim2 = '[' & lookup('field_name', 'match_field_name', 1.01, 'table_name') & ']';

and then within the load:

$(vDim2) as Dim2

In this simple way it wouldn't be really dynamic because you would need to specify each relevant parameter. But such logic might be included within a loop-logic and/or all essential information are listed within a table and horizontally + vertically aggregated and assigned to n variables.

Pure technically is everything possible but you should balance the efforts against the targets and if the resulting complexity is really expedient in the long-term.

[bc1]

understood Marcus, and thanks for taking the time to reply