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: 
Not applicable

Check between dates

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

21 Replies
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Are you able to attach a sample?

Not applicable
Author

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

Not applicable
Author

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);

Not applicable
Author

not working either...

Not applicable
Author

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
Partner - Creator II
Partner - Creator II

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