Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fmazzarelli
Contributor 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
Valued Contributor

Re: case

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
Contributor III

Re: case

try:

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

MVP
MVP

Re: case

I would think that you need to change it to:


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

fmazzarelli
Contributor III

Re: case

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

MVP
MVP

Re: case

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
Contributor III

Re: case

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

MVP
MVP

Re: case

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
Contributor III

Re: case

hi,

no problem.

Thanks anyway.

Employee
Employee

Re: case

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