Calling a function in a function in the load script

[saz]

Hi,

I have some trouble when I need to call a (user-defined) function within a (user-defined) function.

Let me briefly describe the background of my question: I'm dealing with forecasts on some KPI. I would like to define a function which returns said KPI for the latest forecast. Currently, it looks like this

SET fn_getCurrentForecastForKPI = $(fn_getKPI($(var_name), "<$(=$(fn_getMonth($(var_name))))"));

The variable $(var_name) contains the name of the latest forecast and the function fn_getMonth returns the month of validity for the given forecast. The function fn_getKPI does some set analysis based on the name of the forecast and the month of validity. 

However, it does not work as it is supposed to to. When I define the variable as above, then the second parameter (i.e. the date) is not calculated correctly. I just get

$(fn_getKPI('Name of current forecast', ""))

which means that the function fn_getMonth is not being called correctly. How to fix this? I suspect that this is about putting = and $ and the correct places but I don't manage to fix it.

[marcus_sommer]

Nesting variables could become quite tricky - in regards to the various kinds of quotes and even more by including variables which contain any commas - because each comma is treated as parameter-delimiter. Unfortunately there is no way to mask commas.

Workarounds are to replace the commas with another unique char and reversing it again during the call and/or to use different functions/syntax which didn't contain commas (most set analysis could be written without a comma) and/or to design the nesting respectively the variables itself differently. Quite often it's technically possible but the complexity could increase enormously - sometimes more as sensible in regards to the efforts of developing and later maintaining them ...

[rwunderlich]

Are you using the SET statement as shown in script? That would not work as the the $() replacement would get executed in the script. 

-Rob

[saz]

Yes, I'm using the SET statement in the script. I would be fine if the $() would get replaced directly in the script (including calling the function $(get_Month(...)), which is also defined in the script. When loading the script, the name $(var_name) of the latest forecast is indeed being replaced (which is fine), but the function is not being called correctly.

[rwunderlich]

If 

fn_getMonth($(var_name)

is an en expression that tries to get something from the data model, that would not work in script. 

-Rob

[saz]

I see, thanks. Is there any other possibility to solve this? The function fn_getMonth simply looks at the data and returns the first month where data of the forecast exists (essentially, fn_getMonth = Min({1<Forecast={$1}>} Date). My aim is simply to plug the name of the latest forecast and the corresponding month in the function, which calculates the KPI... for me it's hard to imagine that it is impossible to do this in script.

[rwunderlich · Accepted solution]

If you don't need this logic to be sensitive to selections, then you could find the min date in the script using a different technique. However, since I see the $1 in your expression I assume you need it to be dynamic and calculated in the front end.  To define the variable in script without interpreting the $() you will need to escape the $ somehow. A simple approach in this case is to use "@" as a proxy character for $ and replace like this:

Let fn_getCurrentForecastForKPI = Replace(
  '@(fn_getKPI($(var_name), "<@(=@(fn_getMonth(@(var_name))))"))'
,'@','$');

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[saz]

Thanks for this nice suggestion 🙂. Out of curiosity: What would be the best approach if the logic were not sensitive to selections?

[rwunderlich]

It's difficult to give a precise example without know more about your data model and existing script, but something like this to get the Min Date in a variable named vMinDate:

TempDate:
Load Min(Date) as MinDate
Resident YourFacts
Where Forcast = '$(var_name)'
;
Let vMinDate = Peek('MinDate');
Drop Table TempDate;

-Rob