topic Re: sql case condition in qlikview in QlikView

sql case condition in qlikview

Fri, 20 Nov 2020 18:02:30 GMT

Hi Experts,

i have the following sql case statement

CASE when @Name= 'SOT1' THEN

CASE

WHEN (AMT/nullif(TRD,0)*C_TRD) >= TGT1 THEN 75 + (cast(((AMT/nullif(TRD,0)*C_TRD)-TGT1)/250 AS INT)*25)

ELSE 0

END

ELSE

CASE WHEN (AMT/nullif(TRD,0)*C_TRD) >= TGT1 THEN cast((TGT1)*.01

+((AMT/nullif(TRD,0)*C_TRD)-TGT1)*0.1 as int)

ELSE 0

END

(C_TRD is the specific days count in TRD and i did this specific count in qlikview by a variable vCTD)

How can i do this in qlikview.

I m currently trying the expression for the fist case statement as:

=if(NAME='SOT1', IF(SUM(AMT)/(sum(TRD)*sum({<DEC_MonthYear=, DEC_Date= {'vCTD'}>}TRD))>=TGT1

,75 + if(((SUM(AMT)/(sum(TRD)*sum({<DEC_MonthYear=, DEC_Date= {'vCTD'}>}TRD)))-TGT1 )/250,25),0))

But i m not getting any values. please help me on this

Re: sql case condition in qlikview

sasiparupudi1 — Wed, 19 Aug 2015 08:12:12 GMT

why are you doing sum of the columns? in your case statement there is no sum?

also

check group by clause if you properly using it in your load statement.

post your application so that we can help you better

Sasi

Re: sql case condition in qlikview

Wed, 19 Aug 2015 08:37:50 GMT

Hi,

thanks for the response.

i m not using any group by in my script.

i m using the above expression in my pivot table.

even if i remove the sum of the amt, i m not getting any values.

the field TRD and C_TRD is already a summed up field in sql procedure. but in qlikview, it is not summed up in the script, thatsy i m using sum in the expression

Re: sql case condition in qlikview

Peter_Cammaert — Wed, 19 Aug 2015 09:16:31 GMT

Translating SQL code piece-by-piece into somethng that QlikView can handle is never a good idea. Better deduce the reasoning behind this code, and implement the same using QlikView techniques. That way you'll get a solution that is efficient, performs well and can be maintained.

BTW if you don't change the DBMS, almost all SQL code can be copied as-is in a LOAD script. But I have a hunch that you lifted this part from a stored procedure, correct?

Peter

Re: sql case condition in qlikview

hic — Wed, 19 Aug 2015 09:23:14 GMT

I don't understand what your expression really does, but I can see several problems:

You have naked field references inside it (=field references that are not wrapped in an aggregation function) and this is a recipe for problems. See Use Aggregation Functions!
You refer to variables enclosed in single quotes. This will not work. Either you need to use dollar expansion within single quotes, or you use the naked variable without single quotes.
The third If()-function has no comparison in the condition. Is this really correct?

=if(

NAME='SOT1',

IF(

SUM(AMT)/(sum(TRD)*sum({<DEC_MonthYear=, DEC_Date= {'vCTD'}>}TRD)) >= TGT1,

75 + if(((SUM(AMT)/(sum(TRD)*sum({<DEC_MonthYear=, DEC_Date= {'vCTD'}>}TRD)))-TGT1 )/250,25),

)

HIC

Re: sql case condition in qlikview

Wed, 19 Aug 2015 10:00:31 GMT

Peter,

yes, the sql statement is from a procedure

Re: sql case condition in qlikview

Wed, 19 Aug 2015 10:01:13 GMT

Hi Henric,

Thanks.

But, can you please help me with the correct expression

plsss

Re: sql case condition in qlikview

hic — Wed, 19 Aug 2015 10:23:00 GMT

As pcammaert says: "Translating SQL code piece-by-piece into somethng that QlikView can handle is never a good idea." I think it is better that you try to understand the requirement and write this formula in a simpler way.

HIC