Discussion Board for collaboration related to Creating Analytics for QlikView.
Can somebody please help me to fix the below sql expression in qlikview?
Select case when revenue_flag=1 and revenue_amount is not null then null else revenue_amount end as display from HR_LOG.EP_LB_LOG
Go to Solution.
your welcome priya you can mark correct/helpfull answer if my replies did helped you
IF(revenue_flag=1 and NOT(ISNULL(revenue_amount)),NULL(),revenue_amount) AS DISPLAY
if(revenue_flag=1 and isnull(revenue_amount)<>1, ,revenue_amount) as display
Thanks for the script but when I run it, that throws an error of "script line error"
IF(REVENUE_FLAG=1 and NOT(ISNULL(REVENUE_AMOUNT)), NULL(),REVENUE_AMOUNT) AS DISPLAY;
Please let me know if anything is wrong in the script.
I get an error as "Script line error" when I run the script you mentioned. Please let me know if anything is missing.
I could see a red warning after (REVENUE_AMOUNT)<>1,
where are you using this script
in SELECT/SQL Statement with Connection to SQL or in the preceding load
with LOAD statement in qlikview
if you are using SELECT or SQL with SQL connection then you can keep your original SQL query to do the things
Can you please paste your script here or sample app ?
here's the script
LOAD "CE_ID" as [Cost Estimate Number], "OPR_ID" as OPERATION_ID, "SENT_LONGBOW", "SENT_DATE", "INV_COMPLETED", "INV_COMP_DATE", // "ERR_DESC", "REVENUE_FLAG", //Added two revenue fields "REVENUE_AMOUNT"; // "JOB_DATE"; SQL SELECT * FROM "HR_LOG"."EP_LB_LOG" Where SENT_LONGBOW = 'Y';
if(REVENUE_FLAG=1 and isnull(REVENUE_AMOUNT)<>1, REVENUE_AMOUNT) as Display;
LOAD "CE_ID" as [Cost Estimate Number],"OPR_ID" as OPERATION_ID,"SENT_LONGBOW","SENT_DATE","INV_COMPLETED","INV_COMP_DATE",// "ERR_DESC", "REVENUE_FLAG", //Added two revenue fields
if(REVENUE_FLAG=1 and isnull(REVENUE_AMOUNT)<>1, ,REVENUE_AMOUNT) as display,
"REVENUE_AMOUNT";// "JOB_DATE";SQL SELECT *FROM "HR_LOG"."EP_LB_LOG"Where SENT_LONGBOW = 'Y';
Try the above script and see if you still get error try to capture that error or generate log file to see what is exactly wrong with it
or best way is load the data without this display(derived field)
test the expression in list box with expression if(REVENUE_FLAG=1 and isnull(REVENUE_AMOUNT)<>1, ,REVENUE_AMOUNT)
and then use it in the script
Now the script is working fine, I was using it wrongly hence was getting the error. Now am running the whole qvw just to see if everything is fine.
Thank you so much for the help.