Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

appi_priya
New Contributor

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

Tags (2)
1 Solution

Accepted Solutions
bhagirath
Contributor III

Re: CASE Expressions

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

11 Replies

Re: CASE Expressions

maybe

LOAD

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

FROM

HR_LOG.EP_LB_LOG

bhagirath
Contributor III

Re: CASE Expressions

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

appi_priya
New Contributor

Re: CASE Expressions

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
New Contributor

Re: CASE Expressions

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.

bhagirath
Contributor III

Re: CASE Expressions

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

bhagirath
Contributor III

Re: CASE Expressions

Can you please paste your script here or sample app ?

appi_priya
New Contributor

Re: CASE Expressions

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;

bhagirath
Contributor III

Re: CASE Expressions

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
New Contributor

Re: CASE Expressions

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.

Community Browser