Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Check between dates

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

21 Replies
hrlinder
Honored Contributor

Re: Check between dates

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

Not applicable

Re: Check between dates

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

hrlinder
Honored Contributor

Re: Check between dates

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?

Not applicable

Re: Check between dates

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

Not applicable

Re: Check between dates

Try this:

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

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

  as Flag

Not applicable

Re: Check between dates

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

Not applicable

Re: Check between dates

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

Not applicable

Re: Check between dates

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

hrlinder
Honored Contributor

Re: Check between dates

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!