Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

getting error while loading data in script :Not able to load data.

I am trying to load data by creating a variable and assigning set expression to that variable.But after loading it shows me error in loading :unknown error

My variable

Let varCourse_Completed_Count = sum({$<DateType = {completion}, final_test_status={'passed'},client_name={'sdsdcom'},

MonthPeriod1 = {"<$(=Date(MonthStart(weekend(Today())),'MMM-YY'))"}>}course_completed);

Cant i assigned setexpression value to variable.Please help me in this regard

Regards,

Supriya

1 Solution

Accepted Solutions
swuehl
MVP
MVP

There are two issues here:

- QlikSense will try to expand the dollar sign expansion $(=Date(MonthStart(weekend(Today())),'MMM-YY')) in your script, but =Date(MonthStart(weekend(Today())),'MMM-YY' is not an existing variable name, so it can't be expanded.

- Second, your LET statement tells QlikSense to evaluate the part after the equal sign, but sum() is not allowed outside a LOAD statement.

I think you want to assign the expression text as text to the variable, there are multiple approaches to avoid the issues (e.g. load your expression text values from an excel file).

Or try

Let varCourse_Completed_Count = 'sum({$<DateType = {completion}, final_test_status={''passed''},client_name={''sdsdcom''},

MonthPeriod1 = {"<$' &'(=Date(MonthStart(weekend(Today())),''MMM-YY''))"}>}course_completed)';

Note that I've also replaced your existing single quotes with two single quotes.

View solution in original post

6 Replies
Not applicable

Use single Quotes at DateType = {'completion'}

swuehl
MVP
MVP

There are two issues here:

- QlikSense will try to expand the dollar sign expansion $(=Date(MonthStart(weekend(Today())),'MMM-YY')) in your script, but =Date(MonthStart(weekend(Today())),'MMM-YY' is not an existing variable name, so it can't be expanded.

- Second, your LET statement tells QlikSense to evaluate the part after the equal sign, but sum() is not allowed outside a LOAD statement.

I think you want to assign the expression text as text to the variable, there are multiple approaches to avoid the issues (e.g. load your expression text values from an excel file).

Or try

Let varCourse_Completed_Count = 'sum({$<DateType = {completion}, final_test_status={''passed''},client_name={''sdsdcom''},

MonthPeriod1 = {"<$' &'(=Date(MonthStart(weekend(Today())),''MMM-YY''))"}>}course_completed)';

Note that I've also replaced your existing single quotes with two single quotes.

berryandcherry6
Creator II
Creator II
Author

Hi swuehl,

Thankyou very much for your clear explaination and answer,

With your answer i am trying to check condition in if else statement,but i am not getting any value in text box.What is going wrong in code.Please help me.

Let varCourse_Completed_Count = 'sum({$<DateType = {completion}, final_test_status={''passed''},client_name={''sdsdcom''},

MonthPeriod1 = {"<$' &'(=Date(MonthStart(weekend(Today())),''MMM-YY''))"}>}course_completed)';

Let varCourse_Signed_Count = 'count({$<user_signed={1},client_name={"sdsdcom"}, DateType = {''signed''},

MonthPeriod1 = {"<$' &'(=Date(MonthStart(weekend(Today())),''MMM-YY''))"}>}user_signed)';

If(Sign($(varCourse_Signed_Count) - $(varCourse_Completed_Count))=-1) OR (Sign($(varCourse_Signed_Count) - $(varCourse_Completed_Count))= 0) THEN

LET vPartial_Count_Weekly = 0.1 ;

ELSE

LET vPartial_Count_Weekly = ($(varCourse_Signed_Count)-$(varCourse_Completed_Count));

ENDIF

Regards,

Supriya R

swuehl
MVP
MVP

Again, the issues I've discussed in my previous answer also apply here:

Even if the variables would expand correctly in your IF THEN ELSE statement, aggregation functions are not allowed outside a LOAD statement (and besides that, set analysis doesn't work in the script neither).

berryandcherry6
Creator II
Creator II
Author

cant i overcome that issue by using single quotes as you have suggested in previous answer like this

If((Sign('$(varCourse_Signed_Count)' - '$(varCourse_Completed_Count)')=-1) OR (Sign('$(varCourse_Signed_Count)' - '$(varCourse_Completed_Count)')= 0)) THEN

LET vPartial_Count_Weekly = 0.1 ;

ELSE

LET vPartial_Count_Weekly = ('$(varCourse_Signed_Count)'-'$(varCourse_Completed_Count)');

ENDIF

After trying this,

even if data loads now,instead of giving script line error .In text box it gives undefined for vPartial_Count_Weekly.

Is there any possible way?

swuehl
MVP
MVP

If you use single quotes around the dollar sign expansions, this would just indicate a text literal.

But you can't do arithmetics like subtraction on text literals.

If you want to aggregate field values in the script, you need to use a LOAD statement with a GROUP BY clause (and potentially a WHERE clause to filter your records).