Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
appi_priya
Contributor III
Contributor III

CASE Expressions

HI

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

Thanks

1 Solution

Accepted Solutions
Anonymous
Not applicable

your welcome priya
you can mark correct/helpfull answer if my replies did helped you

View solution in original post

11 Replies
robert_mika
Master III
Master III

maybe

LOAD

IF(revenue_flag=1 and NOT(ISNULL(revenue_amount)),NULL(),revenue_amount) AS DISPLAY

FROM

HR_LOG.EP_LB_LOG

Anonymous
Not applicable

if(revenue_flag=1 and isnull(revenue_amount)<>1, ,revenue_amount) as display

appi_priya
Contributor III
Contributor III
Author

Hi Robert,

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.

Thanks.

appi_priya
Contributor III
Contributor III
Author

Hi Bhagirath,

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,

Thanks.

Anonymous
Not applicable

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

Anonymous
Not applicable

Can you please paste your script here or sample app ?

appi_priya
Contributor III
Contributor III
Author

Hi Bhagirath,

here's the script

[Table1]:

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;

Anonymous
Not applicable

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

appi_priya
Contributor III
Contributor III
Author

Hi Bhagirath,

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.