Error when loading variables from database

[#1]

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

[sunny_talwar]

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), '#,###.')

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

[#3]

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

[sasiparupudi1]

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

[sasiparupudi1]

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

[#6]

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