I am very new to Qlik Sense and trying to translate a sql script into Qlik Sense.
I am struggleing to translate this sql section:
sub CFK_NETTO_DURATION (al_start, al_end, sh_start, sh_end, al_known, pnode) sql with RETURN NUMBER IS BEGIN RETURN GREATEST ( 0, CASE WHEN al_end IS NULL THEN AL_NETTO_DURATION (pnode, GREATEST (sh_start, al_start), LEAST (sh_end, SYSDATE)) WHEN al_known IS NULL THEN AL_NETTO_DURATION (pnode, GREATEST (sh_start, al_start), LEAST (sh_end, al_end)) ELSE AL_NETTO_INTERVAL (al_start, al_end, sh_start, sh_end, al_known, pnode) END); END Sub;
Since I didnt find a real solution for the sql case i tried it with serveral IF statements.
Sub CFK_NETTO_DURATION (al_start, al_end, sh_start, sh_end, al_known, pnode, vHeute) [AL_NETTO_DURATION]:
load
If(IsNull('$(al_end)'), $(pnode) ) as $(pnode),//$(pnode), If(IsNull('$(al_end)'), RangeMax($(sh_start),$(al_start))) as REAL_TIME_START, If(IsNull('$(al_end)'), RANGEMin($(sh_end), '$(vHeute)')) as REAL_TIME_END,
If(IsNull('$(al_known)'), $(pnode) ) as pnode_netto, // $(pnode) If(IsNull('$(al_known)'), RangeMax($(sh_start), $(al_start))) as REAL_TIME_START_NETTO, If(IsNull('$(al_known)'), Rangemin($(sh_end), $(al_end))) as REAL_TIME_END_NETTO,
IF(not IsNull('$(al_known)'), $(al_start)) as $(al_start), IF(not IsNull('$(al_known)'), $(al_end)) as $(al_end), IF(not IsNull('$(al_known)'), $(sh_start)) as $(sh_start), IF(not IsNull('$(al_known)'), $(sh_end)) as $(sh_end), IF(not IsNull('$(al_known)'), $(al_known)) as $(al_known), IF(not IsNull('$(al_known)'), $(pnode)) as Pnode3 //'$(pnode)' From [lib://CALC_ALARMS_tmp.qvd] (qvd); End Sub
I want to get all the PNODE as $(pnode) but if i do that i get the error : Field names must be unique within table
Does someone have an idea how to deal with this SQL Case more efficently ?
I think that you may be trying to overcomplicate things here.
There are a couple of things I can see in the script that could be issues;
The IsNull functions will always be false, due to the single quotes If there are spaces in any variable names it will fail (add [] around field names) If any of the field name variables are the same you will get the duplicate field error
Does this need to be in a Sub in the first place, are you calling it many times?
Are the field names changing all the time, to need to be parameterised, or can these be fixed?
How can i correcly translate the sql isnull functions? Sadly I didnt write the SQL script and there are no comments, so i dont know if i can use fixed field names. I will try to cope with different names.
Remove the single quotes inside the IsNull. This will only work if the content of the al_known variable is the name of a field in the QVD and has no spaces. If it has spaces add [ ].
SQL and Qlik loads are very different beasts, as there is unlikely to be a one to one translation.
What fields are in your QVD and what parameters are you calling the sub with? Do the parameters change on each call, or are they static? If static remove the sub and just do inline and use the actual names, not parameters.
