statement case

[fmazzarelli]

hi,

I do not understand where it can be the error. Could you help me?

Thanks

Fabio M.

[SCADENZIARIO]:

LOAD

SCADUTIOLTRE120

SCADUTI120

SCADUTI90

SCADUTI60

SCADUTI30

ASCADERE30

ASCADERE60

ASCADERE90

ASCADERE120

SQL SELECT

year(XLSCAD)*100+Month(XLSCAD) "Annomese",

case

  when XLSCAD < (current date - 5 month) then "SCADUTIOLTRE120"

  when XLSCAD < (current date - 4 Month) then "SCADUTI120"

  when XLSCAD < (current date - 3 month) then "SCADUTI90"

  when XLSCAD < (current date - 2 month) then "SCADUTI60"

  when XLSCAD < (current date - 1 month) then "SCADUTI30"

  when XLSCAD > (current date + 1 month) then "ASCADERE30"

  when XLSCAD > (current date + 2 month) then "ASCADERE60"

    when XLSCAD > (current date + 3 month) then "ASCADERE90"

  when XLSCAD > (current date + 4 month) then "ASCADERE120"

else 0

  END

  "SCAGLIONI",

....

[maxgro]

I think you can try with the script below

SCADUTIOLTRE120, SCADUTI120, SCADUTI90, etc...

are the possible values of the field SCAGLIONI, they are not fields.

[SCADENZIARIO]:

LOAD

     *;

SQL SELECT

year(XLSCAD)*100+Month(XLSCAD) "Annomese",

case

  when XLSCAD < (current date - 5 month) then "SCADUTIOLTRE120"

  when XLSCAD < (current date - 4 Month) then "SCADUTI120"

  when XLSCAD < (current date - 3 month) then "SCADUTI90"

  when XLSCAD < (current date - 2 month) then "SCADUTI60"

  when XLSCAD < (current date - 1 month) then "SCADUTI30"

  when XLSCAD > (current date + 1 month) then "ASCADERE30"

  when XLSCAD > (current date + 2 month) then "ASCADERE60"

    when XLSCAD > (current date + 3 month) then "ASCADERE90"

  when XLSCAD > (current date + 4 month) then "ASCADERE120"

else 0

  END

  "SCAGLIONI",

[ramoncova06]

what error are you getting ?

at the moment I am only seeing that you are missing the ";" at the end of your load

[fmazzarelli]

hi,

i insert the statement case into DataScadenza field but I received the error attached

[fmazzarelli]

ops, you are right.

I changed the code but doesn't work

[maxgro]

it's difficult to help if you only post some rows of the sql part

I suggest to keep it simple (the sql),

SQL select *

from yourtable

where .....;

to begin and when you get the data from the database

add the qlikview load

load

     ......

     ......

     ;

SQL select *

from yourtable

where .....;

[#7]

Hi,

1- The "case when" statements are SQL statements, you can use it in the script within a SQL SELECT but not in the expression editor.

2- You have two consecutive commas :

Regards,

Xavier.

[fmazzarelli]

hi,

my code is:

[SCADENZIARIO]:

LOAD

  Annomese

, SCAGLIONI

, DataScadenza             

;

SQL SELECT

year(XLSCAD)*100+Month(XLSCAD) "Annomese",

case

  when XLSCAD < (current date - 5 month) then SCADUTIOLTRE120

  when XLSCAD < (current date - 4 month) then SCADUTI120

  when XLSCAD < (current date - 3 month) then SCADUTI90

  when XLSCAD < (current date - 2 month) then SCADUTI60

  when XLSCAD < (current date - 1 month) then SCADUTI30

  when XLSCAD > (current date + 1 month) then ASCADERE30

  when XLSCAD > (current date + 2 month) then ASCADERE60

    when XLSCAD > (current date + 3 month) then ASCADERE90

  when XLSCAD > (current date + 4 month) then ASCADERE120

else

END

SCAGLIONI

,XLSCAD     "DataScadenza"             

FROM PIPPO;

[fmazzarelli]

hi,

if i unselect the statement CASE, the script works.

Perhaps, Qlik Sense don't accept the statement CASE? How could I substitute if using if?

I need an example... thanks

Bye for now

======================================================

SQL SELECT

year(XLSCAD)*100+Month(XLSCAD) "Annomese"

// case

// when XLSCAD < (current date - 5 month) then "SCADUTIOLTRE120"

//   when XLSCAD < (current date - 4 month) then ,"SCADUTI120"

//   when XLSCAD < (current date - 3 month) then ,"SCADUTI90"

//   when XLSCAD < (current date - 2 month) then ,"SCADUTI60"

//   when XLSCAD < (current date - 1 month) then ,"SCADUTI30"

//   when XLSCAD > (current date + 1 month) then ,"ASCADERE30"

//   when XLSCAD > (current date + 2 month) then ,"ASCADERE60"

//      when XLSCAD > (current date + 3 month) then ,"ASCADERE90"

//   when XLSCAD > (current date + 4 month) then ,"ASCADERE120"

// else

// END

// ,SCAGLIONI  "SCAGLIONE"

,XLTIPO     "Tip"

[maxgro]

after SQL Sense doesn't matter, is your db that send back the error to Sense

I suppose you need something after else

else

END

maybe

else NULL

END

or maybe 

else 'aaaaaa'

END