11 Replies Latest reply: Oct 8, 2015 4:38 AM by Fabio Mazzarelli RSS

    case

    Fabio Mazzarelli

      Hi Community,

      could you help me?

      Wrong is the bold line.

       

       

      SQL SELECT

      GETDATE ()  AS "Data Giorno",

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

      case

      when xlscad < ("Data Giorno" - 4 Month - 1 day)

            then 'SCADUTO DA OLTRE 120'

      when xlscad between (current_date - 4 Month) and (current_date - 3 Month - 1 day)

            then 'SCADUTO DA 120'

      WHEN xlscad between (current_date - 3 month) and (current_date - 2 Month - 1 day)

            then 'SCADUTO DA 90'

      WHEN xlscad between (current date - 2 Month) and (current_date - 1 month - 1 day)

            then 'SCADUTO DA 60'

      when xlscad between (current_date -1 month) and (current_date)

            then 'SCADUTO DA 30'

      when xlscad between (current_date) and (current_date + 1 month)

            then 'A SCADERE A 30'

      when xlscad between (current_date + 1 Month + 1 day) and (current_date + 2 month)

            then 'A SCADERE A 60'

      when xlscad between (current_date + 2 month + 1 day) and (current_date + 3 month)

            then 'A SCADERE A 90'

      when xlscad between (current_date + 3 month + 1 day) and (current_date + 4 month)

            then 'A SCADERE A 120'

      when xlscad > (current_date + 4 month + 1 day)

            then 'A SCADERE OLTRE 120'

      Else

      'ZERO'

      END

      "SCAGLIONI"

        • Re: case
          Peter Turner

          Hi Fabio,


          This is more a SQL question than a QlikSense question.

          But i think the problem is your field 'Data Giorno' has not been created when you try to use it in the case statement.

          You could try to replace that with the GETDATE() function, or even move all the case statement into a QV script statement and use the IF() function instead, although as you've already written the SQL it might be better to stick with that.

          • Re: case
            Patric Amatulli

            try:

            when xlscad < (GETDATE() - 4 Month - 1 day)

            • Re: case
              Petter Skjolden

              I would think that you need to change it to:


              when xlscad < (current_date - 4 Month - 1 day)

                • Re: case
                  Fabio Mazzarelli

                  SQL SELECT

                  CONVERT(VARCHAR(10),GETDATE(),112) AS "Data Giorno",

                  year("Data Documento")*100+ Month("Data Documento") "Annomese",

                  case

                  when "Data Documento" < (GETDATE() -4 Month -1 day)

                  END

                  "SCAGLIONI"

                   

                  Si è verificato il seguente errore:

                  ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Sintassi non corretta in prossimità di 'Month'.

                    • Re: case
                      Petter Skjolden

                      In SQL you cant reference an aliased column in the same SELECT statement. BTW that is exactly how it works in a LOAD statement too.

                       

                      You are making an ALIAS here:

                       

                      GETDATE ()  AS "Data Giorno"


                      and later in the same select you try to use this ALIAS - that does not work. So just stick to GetDate() all the way through for your entire SELECT:



                      SQL SELECT

                      GETDATE ()  AS "Data Giorno",

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

                      case

                      when xlscad < (GetDate() - 4 Month - 1 day)

                            then 'SCADUTO DA OLTRE 120'

                      when xlscad between (current_date - 4 Month) and (current_date - 3 Month - 1 day)

                            then 'SCADUTO DA 120'

                      WHEN xlscad between (current_date - 3 month) and (current_date - 2 Month - 1 day)

                            then 'SCADUTO DA 90'

                      WHEN xlscad between (current date - 2 Month) and (current_date - 1 month - 1 day)

                            then 'SCADUTO DA 60'

                      when xlscad between (current_date -1 month) and (current_date)

                            then 'SCADUTO DA 30'

                      when xlscad between (current_date) and (current_date + 1 month)

                            then 'A SCADERE A 30'

                      when xlscad between (current_date + 1 Month + 1 day) and (current_date + 2 month)

                            then 'A SCADERE A 60'

                      when xlscad between (current_date + 2 month + 1 day) and (current_date + 3 month)

                            then 'A SCADERE A 90'

                      when xlscad between (current_date + 3 month + 1 day) and (current_date + 4 month)

                            then 'A SCADERE A 120'

                      when xlscad > (current_date + 4 month + 1 day)

                            then 'A SCADERE OLTRE 120'

                      Else

                      'ZERO'

                      END

                      "SCAGLIONI"

                        • Re: case
                          Fabio Mazzarelli

                          hI,

                           

                          i0m trying but i receive the same syntax error .

                           

                          case

                          when "Data Documento" < (GETDATE() - 4 month - 1 day)

                            then 'SCADUTO DA OLTRE 120'

                          when "Data Documento" between (current_date - 4 Month) and (current_date - 3 Month - 1 day)

                            then 'SCADUTO DA 120'

                          WHEN "Data Documento" between (current_date - 3 month) and (current_date - 2 Month - 1 day)

                            then 'SCADUTO DA 90' 

                          WHEN "Data Documento" between (current_date - 2 Month) and (current_date - 1 month - 1 day)

                            then 'SCADUTO DA 60'

                          when "Data Documento" between (current_date -1 month) and (current_date)

                            then 'SCADUTO DA 30'

                          when "Data Documento" between (current_date) and (current_date + 1 month) 

                            then 'A SCADERE A 30'

                          when "Data Documento" between (current_date + 1 Month + 1 day) and (current_date + 2 month)

                            then 'A SCADERE A 60'

                          when "Data Documento" between (current_date + 2 month + 1 day) and (current_date + 3 month)

                            then 'A SCADERE A 90'

                          when "Data Documento" between (current_date + 3 month + 1 day) and (current_date + 4 month)

                            then 'A SCADERE A 120'

                          when "Data Documento" > (current_date + 4 month + 1 day)

                            then 'A SCADERE OLTRE 120'

                          Else

                          'ZERO'

                          END

                          "SCAGLIONI"

                           

                          Si è verificato il seguente errore:

                          ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Sintassi non corretta in prossimità di 'month'.

                    • Re: case
                      Clever Anjos

                      As I can notice you´re using SQL Server, right?

                      (GETDATE() -4 Month -1 day) is not the right way of doing dates math

                      I´m guessing that

                      DateAdd(day,-1,DateAdd(Month,-4,getdate()))

                      is what you need