topic Re: Help with script variable usage in QlikView

Help with script variable usage

tschullo — Mon, 28 Sep 2015 18:30:24 GMT

FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273','1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'
myFlag=index(chr(39) & $(wNums) & chr(39),',');

if $(myFlag) > 0 Then

LET wClause = 'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums) & chr(39),',',2);
else

LET wClause = '=' & $(wNums);
end if;

//wClause = if(index($(wNums),','),'BETWEEN ' & subfield( $(wNums) ,',',1) & ' AND ' & subfield( $(wNums) ,',',2),'=' & $(wNums));
//wClause = if(index(chr(39) & $(wNums) & chr(39),','),'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums)

next wNums;

I thought this would be simple and straightforward.

I just want to dynamically produce a series of WHERE clauses based on parameters passed in on a FOR EACH loop.

It's annoying that the single quotes are stripped, is their a way to not lose them?

As you can tell by commented out code I have tried this several ways now. Any suggestions would be greatly appreciated,

including reading material to explain the ins and outs of using variables in a script.

Also, in a FOR EACH command, is it possible to break the parameters out into more than one line?FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273',

'1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'

Script editor not happy when I do that.

Thanks,

Tony

Re: Help with script variable usage

pamaxeed — Mon, 28 Sep 2015 18:42:24 GMT

Hi,

try to use something like that:

Let strWithoutQuote= ' where company in (#C1#,#C2#)';

Let strWithSingleQuote = replace(strSQL,'#',Chr(39));

and the result will be:

strWhereCluase= where company in ('C1','C2')

Cheers,

Patric

Re: Help with script variable usage

maxgro — Mon, 28 Sep 2015 18:56:26 GMT

FOR EACH wNums in '76','1226,1250','1251,1255','1256,1268','1269,1273','1274,1280','1281,1290','1291,1300','1301,1311','1312,1321','1322,1335','1336,1345'

//myFlag=index(chr(39) & $(wNums) & chr(39),',');

let myFlag=index('$(wNums)', ',');

//trace vNums=$(wNums) myFlag=$(myFlag);

if $(myFlag) > 0 Then

//LET wClause = 'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums) & chr(39),',',2);

LET wClause = 'BETWEEN ' & subfield('$(wNums)',',',1) & ' AND ' & subfield('$(wNums)',',',2);

else

//LET wClause = '=' & $(wNums);

LET wClause = '=$(wNums)';

end if;

//wClause = if(index($(wNums),','),'BETWEEN ' & subfield( $(wNums) ,',',1) & ' AND ' & subfield( $(wNums) ,',',2),'=' & $(wNums));

//wClause = if(index(chr(39) & $(wNums) & chr(39),','),'BETWEEN ' & subfield(chr(39) & $(wNums) & chr(39),',',1) & ' AND ' & subfield(chr(39) & $(wNums)

TRACE wClause $(wClause);

next wNums;

Re: Help with script variable usage

pamaxeed — Mon, 28 Sep 2015 18:58:28 GMT

In your example something like that:

FOR EACH wNums in '#1226,1250#' //,'#1251,1255#','#1256,1268#','#1269,1273#','#1274,1280#','#1281,1290#','#1291,1300#','#1301,1311#','#1312,1321#','#1322,1335#','#1336,1345#'

LET wClause = '=' & replace(wNums,'#',Chr(39));

next wNums;

Note to get the string value of each for entry do not use $(wNums) but just wNums.

Cheers,

Patric

Re: Help with script variable usage

tschullo — Mon, 28 Sep 2015 19:07:05 GMT

Dude, you rock!