topic Re: Check between dates in QlikView

Check between dates

Mon, 28 Jul 2014 14:51:30 GMT

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

Re: Check between dates

Anonymous — Mon, 28 Jul 2014 15:02:05 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:08:59 GMT

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

Re: Check between dates

Anonymous — Mon, 28 Jul 2014 15:12:35 GMT

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?

Re: Check between dates

Mon, 28 Jul 2014 15:16:04 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:16:27 GMT

Try this:

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

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

as Flag

Re: Check between dates

Mon, 28 Jul 2014 15:20:38 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:21:35 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:24:11 GMT

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

Re: Check between dates

Anonymous — Mon, 28 Jul 2014 15:24:15 GMT

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)

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!

Re: Check between dates

Mon, 28 Jul 2014 15:26:42 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:27:50 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:36:32 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:39:10 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 15:40:35 GMT

Are you able to attach a sample?

Re: Check between dates

Mon, 28 Jul 2014 15:46:34 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 16:02:16 GMT

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

Re: Check between dates

Mon, 28 Jul 2014 16:06:26 GMT

not working either...

Re: Check between dates

Mon, 28 Jul 2014 16:21:06 GMT

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

Re: Check between dates

partenope — Mon, 28 Jul 2014 16:26:35 GMT

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