Equivalent to LOOKUP or APPLYMAP in charts or variables

[#1]

Both functions LOOKUP and APPLYMAP can only be used in the script according to what I have been finding here in the forums.

I would need the equivalent of them to apply to a variable.

The situation is:

a) there is a special financial calendar where there is a direct relationship between the WeekID and the MonthID

Meaning: for every WeekID there is one specific MonthID. For example:

WeekID MonthID

---------------------------

3 15

4 15

5 16

6 16

7 16

b) The user selects one week ( vSelectedWeek ) and the variable vSelectedMonth should calculate the respective MonthID

Something like:

lookup the value in the column MonthID

when the value in the column WeekID

is equal to the variable vSelectedWeek

in the table with those columns TABLE_WEEKID_MONTHID

vSelectedMonth =Lookup( 'MonthID', 'WeekID', $(SelectedWeek), 'TABLE_WEEKID_MONTHID' )

In the script, this is a classical situation for the MAPPING LOAD and APPLYMAP but the problem is that the vSelectedMonth is a variable to be dinamically adjusted by the user and (here is the difficulty) only indirectly through the variable vSelectedWeek.

Any ideas?

Thank you

[#2 · Accepted solution]

Hi,

in the definiction of your variable vSelectedMonth write something like:

=only({$<WeekID={'$(vSelectedWeek)'}>} MonthID)

Hope this helps

Regards!

[#3]

Hola Gabriela,

That's a very clever trick.

It does the "mapping" by filtering the list (with the Set Analysis) and on top verifies that there is only one possible value (with the only() function).

If the table really has 1:1 relationship (meaning in the example that for every week there is only one month) then any functions is possible .

The only correction is: I don't use the literal quotes around the variable $(vSelectedWeek).

I've used:

=only({<WeekID={$(vSelectedWeek)}>} MonthID)

(I also usually don't use the $ in the Set Analysis expression)

Gracias!