topic Re: CASE Expressions in QlikView

CASE Expressions

appi_priya — Fri, 03 Apr 2015 06:00:04 GMT

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

Re: CASE Expressions

robert_mika — Fri, 03 Apr 2015 06:25:25 GMT

maybe

LOAD

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

FROM

HR_LOG.EP_LB_LOG

Re: CASE Expressions

Anonymous — Fri, 03 Apr 2015 06:26:43 GMT

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

Re: CASE Expressions

appi_priya — Fri, 03 Apr 2015 08:30:07 GMT

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.

Re: CASE Expressions

appi_priya — Fri, 03 Apr 2015 08:34:43 GMT

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.

Re: CASE Expressions

Anonymous — Fri, 03 Apr 2015 08:36:01 GMT

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

Re: CASE Expressions

Anonymous — Fri, 03 Apr 2015 08:36:54 GMT

Can you please paste your script here or sample app ?

Re: CASE Expressions

appi_priya — Fri, 03 Apr 2015 08:48:01 GMT

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;

Re: CASE Expressions

Anonymous — Fri, 03 Apr 2015 08:53:35 GMT

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

Re: CASE Expressions

appi_priya — Fri, 03 Apr 2015 09:32:48 GMT

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.

Re: CASE Expressions

Anonymous — Fri, 03 Apr 2015 09:34:32 GMT

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

Re: CASE Expressions

appi_priya — Fri, 03 Apr 2015 09:35:44 GMT

Hi Robert,

I realized that I was using the script wrongly and checked that after placing the condition in the preceeding load with the LOAD statement I got the desired result.

Thank you so much for the help.