Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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))'
Have you tried loading the expression field into table box or a texbox? Is the formula text displaying fine those objects?
I would go with the option Syed has proposed. Replace $ with some other special character in the db use replace function in qlikview
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