topic Re: Variable troubles in QlikView

Variable troubles

Fri, 07 Mar 2014 12:21:54 GMT

Alright chaps.

I have a variable...

LET vMaxdate = '=max(MonthStart(prvMonthEnd))';

In a for loop I want to end up with...

vMaxdate_1=AddMonths('$(vMaxdate)',-11)

vMaxdate_2=AddMonths('$(vMaxdate)',-10)

vMaxdate_3=AddMonths('$(vMaxdate)',-9) etc.

FOR a = 1 to 12

LET b = 12-$(a);

LET vMaxdate_$(a) = '=AddMonths($(vMaxdate),-$(b))';

NEXT a;

But I am getting the text of vMaxdate in each variable...

vMaxdate_1=AddMonths(=max(MonthStart(prvMonthEnd)),-11)

vMaxdate_2=AddMonths(=max(MonthStart(prvMonthEnd)),-10)

vMaxdate_3=AddMonths(=max(MonthStart(prvMonthEnd)),-9) etc.

So how do I force the variable name rather than its expression into the second set of variables?

Thanks.

Foldy

Re: Variable troubles

alexandros17 — Fri, 07 Mar 2014 12:29:59 GMT

I should try this:

LET vMaxdate = max(MonthStart(prvMonthEnd));

FOR a = 1 to 12

LET b = 12-$(a);

LET vMaxdate_$(a) = AddMonths($(vMaxdate),-$(b));

NEXT a;

Re: Variable troubles

Fri, 07 Mar 2014 12:40:33 GMT

Thanks but that's not quite what I wanted. I think that will try and evaluate the AddMonths whereas I want the AddMonths as text along with the variable $(vMaxdate)...

Re: Variable troubles

MarcoWedel — Fri, 07 Mar 2014 12:41:43 GMT

Hi foldyman,

you might also consider using a single parameterized variable instead of the whole creating multible variables in a loop concept:

SET vMaxdate = '=AddMonths(max(MonthStart(prvMonthEnd)),'&$1&'-12)';

which could be used like

$(vMaxdate(1)) for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-11)'

$(vMaxdate(2)) for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-10)'

$(vMaxdate(3)) for a value of '=AddMonths(max(MonthStart(prvMonthEnd)),-9)'

...

which then could be calculated using this syntax:

$(=$(vMaxdate(1)))

hope this helps

regards

Marco

Re: Variable troubles

maxgro — Fri, 07 Mar 2014 21:55:56 GMT

SCRIPT

LET vMaxdate = 'max(MonthStart(prvMonthEnd))';

FOR a = 1 to 12

LET b = 12-$(a);

LET vMaxdate_$(a) = '=AddMonths(' & chr(39) & '$' & '(vMaxdate)' & chr(39) & ',-$(b))';

NEXT a;

RESULT

Re: Variable troubles

Mon, 10 Mar 2014 09:29:11 GMT

Thanks Marco. Never seen parameterized variables before. I tried it out on my dash but it seems in this instance that performance really suffers. I'll be keeping the idea in my code snippets bank for the future tho. Thanks.

Re: Variable troubles

Mon, 10 Mar 2014 09:35:04 GMT

Thanks Massimo, that seems to have done it though I didn't need the chr(39)'s. But...

why doesn't the first line evaluate the same as the second?

	'=AddMonths($(vMaxdate),-$(b))';
	'=AddMonths(' & '$' & '(vMaxdate)' & ',-$(b))';

It seems to me (a .net developer) that they should be the same as the second is just concatenated pieces of text?

Re: Variable troubles

MarcoWedel — Wed, 12 Mar 2014 22:23:24 GMT

interesting, I never noticed any performance influence of this.

Did you intentionally mark your own comment helpful?

😉

regards

Marco

Re: Variable troubles

Tue, 18 Mar 2014 10:54:50 GMT

Errr yes. obviously getting click happy in my old age. Unmarked now tho.

Perhaps I was doing something wrong - I'll try it again. Thanks.