Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fmazzarelli
Partner - Creator III
Partner - Creator III

case

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"

11 Replies
peter_turner
Partner - Specialist
Partner - Specialist

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.

pamaxeed
Partner - Creator III
Partner - Creator III

try:

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

petter
Partner - Champion III
Partner - Champion III

I would think that you need to change it to:


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

fmazzarelli
Partner - Creator III
Partner - Creator III
Author

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

petter
Partner - Champion III
Partner - Champion III

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"

fmazzarelli
Partner - Creator III
Partner - Creator III
Author

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

petter
Partner - Champion III
Partner - Champion III

ha ha - now you show a different SQL - and it is not even a complete SELECT statement 😉

So I cant judge if you are continuing to reference an alias ...

I am not a human syntax checker - you have to put in some effort yourself - honestly. Read the community guidelines about posting ....

fmazzarelli
Partner - Creator III
Partner - Creator III
Author

hi,

no problem.

Thanks anyway.

Clever_Anjos
Employee
Employee

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