Skip to main contentSign InHelp

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can learn more about Qlik Sense App Development and Usage.

- Qlik Community
- :
- Forums
- :
- Data Analytics
- :
- Qlik Sense
- :
- App Development
- :
- $ expansion of a conditional function in load edit...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

danaleota1

Creator

2021-06-14
03:46 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

$ expansion of a conditional function in load editor, load as a field

Hello I have this variable

Set f_GroupScore =

'If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore1,

1,

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore2,

2,

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore3,

3,

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore4,

4,

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore5,

5,

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore0,

1,

Null()

)

)

)

)

)

)';

I am trying to load it as a field but in the table it isn't expanding. I loaded it as:

AHT:

Load

KEY_IdDate,

REPORT_GROUP,

GroupScore1,

GroupScore2,

GroupScore3,

GroupScore4,

GroupScore5,

GroupScore0,

ACDCALLS,

ACDTIME,

HOLDTIME,

ACWTIME,

TOTALACDCALLS,

ACDCALLS/TOTALACDCALLS as Weight,

'$(f_GroupScore)'*(ACDCALLS/TOTALACDCALLS) as Weight1

resident Temp2_AHT

;

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.

Thanks,

1 Solution

Accepted Solutions

marcus_sommer

MVP & Luminary

2021-06-15
05:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

The variable is neither properly created nor called - try it with (here simplified for the syntax):

set exp = "sum(Field)";

t: load F1, F2, $(exp) as F3 from Source **group by** F1, F2;

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.

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.

- Marcus

66 Views

2 Replies

marcus_sommer

MVP & Luminary

2021-06-15
05:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

The variable is neither properly created nor called - try it with (here simplified for the syntax):

set exp = "sum(Field)";

t: load F1, F2, $(exp) as F3 from Source **group by** F1, F2;

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.

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.

- Marcus

67 Views

danaleota1

Creator

2021-06-16
02:59 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi Marcus,

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.

Thanks!