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??

[Anonymous]

Hi,

you should use if instead of case when

if ((D031... -D011...) > 3 then 'Tempo',

   if ((do31.. - D011...) > 7 then 'Ritardo'
)) as Text

Hope it helps

[#3]

I don't think is the correct syntax...it's not working...

[Anonymous]

what is the error? you need to put the above script when you read the table

Table:

load

D031....,

D011...

if (D031-D011 > 3, ...

..

resident abc;

WHat are D031... and D011? Fields within a table?

Can you send example?

[#5]

DO31 and DO11 are two tables already loaded within the script, so I'm using two fields: DO31_DATADELIVERY and DO11_DATADOCUMENT which are already present in my script. I think it's just a matter of writing properly the 'case when' syntax...

[#6]

Try this:

If([DO31_DATADELIVERY - DO11_DATADOCUMENT]>7, 'In Tempo',

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

  as Flag

[#7]

Note that I also switched the condition to evaluate if field is larger than 7 before 3. If you have the condition evaluate if its over 3, then everything over 3 will be in Tempo. You can also expand the expression to test if field is larger then 3 and smaller than 7 for Tempo like this:

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

[#8]

no it's not working...I quite sure that in script I must use the CASE WHEN syntax instead of  IF...

[#9]

[DO31_DATADELIVERY] and [DO11_DATADOCUMENT] are separate fields that you are subtracting or is it one field called [DO31_DATADELIVERY - DO11_DATADOCUMENT]?

[Anonymous]

there is no case syntax

you need to bring the two table in relation. is there an id which can be used?

TEST:

noconcatenate load
ID
D031_DATADELIVERY
reiseent D031

Left join(TEST)

ID

D011_DATADOCUMENT

resident D011

TEST1:

noconcatenate load

D031_DATADELIVERY,

D011_DATADOCUMENT,

if (D031... - D011 > 3, 'Tempo'

resident TEST;

drop table TEST

this is not exactly the script you need. it should give you an idea!

I am missing CASE when also. It would be nice to have!