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

[berryandcherry6]

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

[#2]

Use single Quotes at DateType = {'completion'}

[swuehl · Accepted solution]

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]

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]

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]

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]

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).