topic Re: SUB and END SUB Syntax? in QlikView

SUB and END SUB Syntax?

NickHoff — Fri, 13 Dec 2013 13:49:08 GMT

What's wrong with this statement, Qlikview keeps giving an error before the load and referencing END SUB, i've tried to rename it to END SUB as well.

SUB CalcMonth

IF CalendarMonthName = 'Jan' THEN

LET $(vSubmonth) = 6;

ELSEIF CalendarMonthName = 'Feb' THEN

LET $(vSubmonth) = 7;

ELSEIF CalendarMonthName = 'Mar' THEN

LET $(vSubmonth) = 8;

ELSEIF CalendarMonthName = 'Apr' THEN

LET $(vSubmonth) = 9;

ELSEIF CalendarMonthName = 'May' THEN

LET $(vSubmonth) = 10;

ELSEIF CalendarMonthName = 'Jun' THEN

LET $(vSubmonth) = 11;

ELSEIF CalendarMonthName = 'Jul' THEN

LET $(vSubmonth) = 12;

ELSEIF CalendarMonthName = 'Aug' THEN

LET $(vSubmonth) = 1;

ELSEIF CalendarMonthName = 'Sep' THEN

LET $(vSubmonth) = 2;

ELSEIF CalendarMonthName = 'Oct' THEN

LET $(vSubmonth) = 3;

ELSEIF CalendarMonthName = 'Nov' THEN

LET $(vSubmonth) = 4;

ELSE

IF CalendarMonthName = 'Dec' THEN

LET $(vSubmonth) = 5;

END IF

END SUB

Re: SUB and END SUB Syntax?

Gysbert_Wassenaar — Fri, 13 Dec 2013 14:15:31 GMT

How are you trying to use that Sub? You cannot use it as a function in a load statement if that's what you're trying to do. Subs are used by calling them using the Call keyword:

Sub MySub

// code here

End Sub

Call MySub;

In your code CalendarMonthName should be a variable. If it's a field name then it won't work.

Re: SUB and END SUB Syntax?

Peter_Cammaert — Fri, 13 Dec 2013 14:22:12 GMT

If this is the exact script syntax you're using, look at the end of your IF construct. There is another IF statement embedded in the ELSE clause, which should be terminated by a second END IF clause. Because that one is missing, QV complains about an END SUB that comes too early.

Or change the ELSE IF into ELSEIF which probably is what you intended to do all along.

Good luck,

Peter

Re: SUB and END SUB Syntax?

NickHoff — Fri, 13 Dec 2013 14:55:12 GMT

I was going to use the sub to declare the variables prior to the load statement, then create an expression variable total savings/eSubmonth in my load statement. I thought you couldn't end a nested if statement without an else

Re: SUB and END SUB Syntax?

marcus_sommer — Fri, 13 Dec 2013 15:09:59 GMT

I think you should rather use a pick(match()) function in your load-statement:

pick(match(CalendarMonthName), 'Jan', 'Feb', ....), 6, 7, ...) as xyz

- Marcus

Re: SUB and END SUB Syntax?

NickHoff — Fri, 13 Dec 2013 15:13:29 GMT

Thanks Marcus, this accomplishes what I was attempting.

Re: SUB and END SUB Syntax?

NickHoff — Fri, 13 Dec 2013 15:18:59 GMT

It's asking for two arguments though, perhaps a syntax problem?:

PICK(MATCH(CalendarMonthName), 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5) as CalcMonth

Re: SUB and END SUB Syntax?

marcus_sommer — Fri, 13 Dec 2013 15:22:20 GMT

Yes it's syntax - try this:

PICK(MATCH(CalendarMonthName, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'), 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5) as CalcMonth

- Marcus