sql case condition in qlikview

[#1]

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

[sasiparupudi1]

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

[#3]

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

[Peter_Cammaert]

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

[hic]

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

1. 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!
2. 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.
3. 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),

        0

        )

    )

HIC

[#6]

Peter,

yes, the sql statement is from a procedure

[#7]

Hi Henric,

Thanks.

But, can you please help me with the correct expression

plsss

[hic]

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