11 Replies Latest reply: Jul 23, 2015 11:05 AM by Fabio Mazzarelli RSS

    statement case

    Fabio Mazzarelli

      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",

      ....

        • Re: statement case
          Massimo Grossi

          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",

            • Re: statement case
              Fabio Mazzarelli

              ops, you are right.

               

              I changed the code but doesn't work

               

              Immagine.png

                • Re: statement case
                  Massimo Grossi

                  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 .....;


                    • Re: statement case
                      Fabio Mazzarelli

                      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;

                        • Re: statement case
                          Fabio Mazzarelli

                          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"

                  • Re: statement case
                    Ramon Covarrubias

                    what error are you getting ?

                     

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

                    • Re: statement case
                      Xavier Retaillaud

                      Hi,

                       

                      I think i understand your logic.

                       

                      Try this:

                       

                      [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 1 else 0 end as "SCADUTIOLTRE120",

                      case when XLSCAD >=  (current date - 5 month) and XLSCAD < (current date - 4 Month) then 1 else 0 end as "SCADUTI120",

                      case whenXLSCAD >=  (current date - 4 month) and XLSCAD < (current date - 3 month) then 1 else 0 end as "SCADUTI90",

                      case when XLSCAD >=  (current date - 3 month) and XLSCAD < (current date - 2 month) then 1 else 0 end as "SCADUTI60",

                      case when XLSCAD >=  (current date - 2 month) and XLSCAD < (current date - 1 month) then 1 else 0 end as"SCADUTI30",

                      case when XLSCAD >=  (current date -1 month) and XLSCAD < (current date + 1 month) then 1 else 0 end as"ASCADERE30",

                      case when XLSCAD >=  (current date + 1 month) and  XLSCAD < (current date + 2 month) then 1 else 0 end as"ASCADERE60",

                      case when XLSCAD >=  (current date + 2 month) and XLSCAD < (current date + 3 month) then 1 else 0 end as"ASCADERE90",

                      case when XLSCAD >= (current date + 3 month) then 1 else 0 end as"ASCADERE120",

                      ....

                      ....;

                       

                      you have to verify the intervals for XLSCAD