topic $ expansion of a conditional function in load editor, load as a field in App Development
https://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815016#M66661
<P>Hello I have this variable </P><P>Set f_GroupScore =<BR />'If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore1,<BR />1,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore2,<BR />2,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore3,<BR />3,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore4,<BR />4,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore5,<BR />5,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore0,<BR />1,<BR />Null()<BR />)<BR />)<BR />)<BR />)<BR />)<BR />)';</P><P> </P><P>I am trying to load it as a field but in the table it isn't expanding. I loaded it as:</P><P>AHT:<BR />Load<BR />KEY_IdDate,<BR />REPORT_GROUP,<BR />GroupScore1,<BR />GroupScore2,<BR />GroupScore3,<BR />GroupScore4,<BR />GroupScore5,<BR />GroupScore0,<BR />ACDCALLS,<BR />ACDTIME,<BR />HOLDTIME,<BR />ACWTIME,<BR />TOTALACDCALLS,<BR />ACDCALLS/TOTALACDCALLS as Weight,<BR />'$(f_GroupScore)'*(ACDCALLS/TOTALACDCALLS) as Weight1<BR />resident Temp2_AHT<BR />;</P><P>I want to expand it in the load editor so I can then filter certain values from showing up in the grids but instead of evaluating the expression it is only returning the string of the expression. I've tried 'set', and 'let', I've tried inputing '=' in the parantheses. Not sure what I'm missing is it not evaluating because it is an if statement? Any help would be appreciated. </P><P> </P><P>Thanks, </P>Mon, 14 Jun 2021 19:46:36 GMTdanaleota12021-06-14T19:46:36Z$ expansion of a conditional function in load editor, load as a field
https://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815016#M66661
<P>Hello I have this variable </P><P>Set f_GroupScore =<BR />'If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore1,<BR />1,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore2,<BR />2,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore3,<BR />3,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore4,<BR />4,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore5,<BR />5,<BR />If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore0,<BR />1,<BR />Null()<BR />)<BR />)<BR />)<BR />)<BR />)<BR />)';</P><P> </P><P>I am trying to load it as a field but in the table it isn't expanding. I loaded it as:</P><P>AHT:<BR />Load<BR />KEY_IdDate,<BR />REPORT_GROUP,<BR />GroupScore1,<BR />GroupScore2,<BR />GroupScore3,<BR />GroupScore4,<BR />GroupScore5,<BR />GroupScore0,<BR />ACDCALLS,<BR />ACDTIME,<BR />HOLDTIME,<BR />ACWTIME,<BR />TOTALACDCALLS,<BR />ACDCALLS/TOTALACDCALLS as Weight,<BR />'$(f_GroupScore)'*(ACDCALLS/TOTALACDCALLS) as Weight1<BR />resident Temp2_AHT<BR />;</P><P>I want to expand it in the load editor so I can then filter certain values from showing up in the grids but instead of evaluating the expression it is only returning the string of the expression. I've tried 'set', and 'let', I've tried inputing '=' in the parantheses. Not sure what I'm missing is it not evaluating because it is an if statement? Any help would be appreciated. </P><P> </P><P>Thanks, </P>Mon, 14 Jun 2021 19:46:36 GMThttps://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815016#M66661danaleota12021-06-14T19:46:36ZRe: $ expansion of a conditional function in load editor, load as a field
https://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815140#M66683
<P>The variable is neither properly created nor called - try it with (here simplified for the syntax):</P><P>set exp = "sum(Field)";</P><P>t: load F1, F2, $(exp) as F3 from Source <STRONG>group by</STRONG> F1, F2;</P><P>By your intended aggregating you will further need an appropriate group by to all fields which aren't included in the aggregations. This means you may not able to apply the aggregating within your origin load because the other fields may prevent the aggregation on the intended level - in this case you need to do the aggregation on the outside and map or join the results together.</P><P>Beside this you use the + operator - if any of the parameter didn't return a valid numeric value the result will be NULL. If this is possible but not intended you should use rangesum() because each non numeric result will be treated as 0.</P><P>- Marcus</P>Tue, 15 Jun 2021 09:51:16 GMThttps://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815140#M66683marcus_sommer2021-06-15T09:51:16ZRe: $ expansion of a conditional function in load editor, load as a field
https://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815603#M66739
<P>Hi Marcus, </P><P>That worked. I changed the null() in my expression to a 0 which works for what I'm intending, and in the subsequent load statement I did the group by like you suggested included all the attributes to group by. I was able to load the variable now as a field and it worked and shows up evaluated in the straight table in my visualizations like I wanted.</P><P> </P><P>Thanks! </P>Wed, 16 Jun 2021 18:59:12 GMThttps://community.qlik.com/t5/App-Development/expansion-of-a-conditional-function-in-load-editor-load-as-a/m-p/1815603#M66739danaleota12021-06-16T18:59:12Z