Check between dates

[#1]

hi everyone, I was trying adding this kind of lines in my script

(CASE (DO31_DATADELIVERY - DO11_DATADOCUMENT)

WHEN (DO31_DATADELIVERY - DO11_DATADOCUMENT)>3 THEN 'In Tempo'

  WHEN (DO31_DATADELIVERY - DO11_DATADOCUMENT)>7 THEN 'Ritardo'

  END)

but I always receive error. But in the chart if I insert the calculation (DO31_DATADELIVERY - DO11_DATADOCUMENT) as an expression, I obtain the number of days between the two dates.

What I'm doing wrong??

[#2]

two separate fields which I want to subtract one from each other

[#3]

Updated.

Ah, try this:

If(([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])> 3 and ([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])<7, 'In Tempo',

      If(([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])>7, 'Ritardo'))

  as Flag

[#4]

not working...the IF syntax shouldn't have END IF btw?

[#5]

No END if you are using within your load statement:

LOAD

...

     If(([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])> 3 and ([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])<7, 'In Tempo', If(([DO31_DATADELIVERY]-[DO11_DATADOCUMENT])>7, 'Ritardo'))

  as Flag

...

FROM

[#6]

Are you able to attach a sample?

[#7]

you won't find it useful, since you cannot use it if you don't have also the SQL DB on which it runs...without the SQL DB you cannot reload the data and whatever change you do to the script won't load

[#8]

OK, are you using SQL Select to load the data? If so, can you try a preceding load, something like this:

LOAD

*,

If((Date#([DO31_DATADELIVERY],'DD/MM/YYYY')-Date#([DO11_DATADOCUMENT],'DD/MM/YYYY'))>3 and (Date#([DO31_DATADELIVERY],'DD/MM/YYYY')-Date#([DO11_DATADOCUMENT],'DD/MM/YYYY'))<7, 'In Tempo',

If((Date#([DO31_DATADELIVERY],'DD/MM/YYYY')-Date#([DO11_DATADOCUMENT],'DD/MM/YYYY'))>7, 'Ritardo'))

as Flag;

SQL SELECT ... (this is your SQL statement);

[#9]

not working either...

[#10 · Accepted solution]

In your SQL statement, can try the following case statement:

SQL SELECT

...
CASE

  WHEN (DO31_DATADELIVERY - DO11_DATADOCUMENT) > 3

      and (DO31_DATADELIVERY - DO11_DATADOCUMENT) < 7 THEN 'In Tempo'

  WHEN (DO31_DATADELIVERY - DO11_DATADOCUMENT) > 7 THEN 'Ritardo'

END as Flag

...

FROM

[partenope]

Hi Irene,

can you post the whole LOAD in witch you are using the CASE statement?

I think you are trying to do something wrong in Qlik...

Is not a CASE or IF problem: the real issue is that you can't use two fields from two different tables on LOAD statement... You need to create a table containing the two fields before...