topic Error when loading variables from database in QlikView

Error when loading variables from database

Fri, 24 Jul 2015 15:48:22 GMT

I'm having an issue when loading variable from our database to QlikView. The formula in the database is

=Num(Sum( { <

TransEffDate={">=$(=Min(x.TransEffDate)) <= $(=Max(x.TransEffDate))"}

,DetailTypeCd={1, 2, 3}

,CancelEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"}

> } AdminPrincipalAmount)

+ Sum( { <

TransEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"}

,DetailTypeCd={4, 5, 6}

,CancelEffDate={">=$(=Min(x.TransEffDate))<=$(=Max(x.TransEffDate))"}

> } AdminPrincipalAmount), '#,###.')

when it's loaded into QlikView I get

=Num(Sum( { <

TransEffDate={">= <= "}

,DetailTypeCd={1, 2, 3}

,CancelEffDate={"<>"}

> } AdminPrincipalAmount)

+ Sum( { <

TransEffDate={"<>"}

,DetailTypeCd={4, 5, 6}

,CancelEffDate={">=<="}

> } AdminPrincipalAmount), '#,###.')

this is the code I'm using to load the variables. I've tried using fieldvalue instyead of peek and it was same results. Seems like everything after the $ gets ignored.

Load

variable_name as Variable,
expression as Expression;
select variable_name, expression
from qlikview_expressions;

Let NumRows = NoOfRows('Expressions');
For i=1 to $(NumRows)
Let vVarName = Peek('Variable',$(i));
Let vExpression = Peek('Expression',$(i));
let $(vVarName) = '$(vExpression)';
NEXT

Re: Error when loading variables from database

sunny_talwar — Fri, 24 Jul 2015 16:15:49 GMT

May be try the expression in Excel file with a single quote (')

variable_name	expression
Name	'=Num(Sum( { < TransEffDate={">=$(=Min(x.TransEffDate)) <= $(=Max(x.TransEffDate))"} ,DetailTypeCd={1, 2, 3} ,CancelEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"} > } AdminPrincipalAmount) + Sum( { < TransEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"} ,DetailTypeCd={4, 5, 6} ,CancelEffDate={">=$(=Min(x.TransEffDate))<=$(=Max(x.TransEffDate))"} > } AdminPrincipalAmount), '#,###.')

variable_name

expression

Name

'=Num(Sum( { <

TransEffDate={">=$(=Min(x.TransEffDate)) <= $(=Max(x.TransEffDate))"}

,DetailTypeCd={1, 2, 3}

,CancelEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"}

> } AdminPrincipalAmount)

+ Sum( { <

TransEffDate={"<$(=Min(x.TransEffDate))>$(=Max(x.TransEffDate))"}

,DetailTypeCd={4, 5, 6}

,CancelEffDate={">=$(=Min(x.TransEffDate))<=$(=Max(x.TransEffDate))"}

> } AdminPrincipalAmount), '#,###.')

Single quote won't be visible, but it will be there when you view the formula by pressing F2 Key

Re: Error when loading variables from database

Fri, 24 Jul 2015 16:22:54 GMT

To overcome this, store '$' char as '@' in DB and in wrap your peek function with Replace or use CHR(36) instead of '$'

E.g: CHR(36) & '(=Min(x.TransEffDate))'

Re: Error when loading variables from database

sasiparupudi1 — Fri, 24 Jul 2015 17:06:04 GMT

Have you tried loading the expression field into table box or a texbox? Is the formula text displaying fine those objects?

Re: Error when loading variables from database

sasiparupudi1 — Fri, 24 Jul 2015 18:42:20 GMT

I would go with the option Syed has proposed. Replace $ with some other special character in the db use replace function in qlikview

Re: Error when loading variables from database

Fri, 24 Jul 2015 20:00:40 GMT

I tried with the @ and the same issue as before. It would cut off everything until the next @. If I put random letters such as bb in place of the @, it the variable was loaded correctly. I tried wrapping a replace function around peek, but when I did this the variables were not populated with the expression, only the variable name was brought back. Is my syntax correct?

Load

variable_name as Variable,
expression as Expression;
select variable_name, expression
from qlikview_expressions;

Let NumRows = NoOfRows('Expressions');
For i=1 to $(NumRows)
Let vVarName = FieldValue('Variable',$(i));
Let vExpression = Replace(bb,FieldValue('Expression',$(i)),$);
let $(vVarName) = '$(vExpression)';
NEXT