topic Re: Intervalmatch not working well in QlikView

Intervalmatch not working well

Sun, 02 Sep 2012 09:59:49 GMT

Any idea what I am doing wrong?

See attached file

Thanks in afdvance

Re: Intervalmatch not working well

disqr_rm — Sun, 02 Sep 2012 11:41:56 GMT

It's because your timestamps aren't formatted properly as internal timestamp format. Just change your Schema table as follows and you'll be fine.

Schema:

LOAD Name, Datum, timestamp#(From, 'DD-MM-YYYY hh:mm') as From, timestamp#(Until, 'DD-MM-YYYY hh:mm') as Until;

LOAD * INLINE [

Name, Datum, From, Until

1, 05-06-2012, 05-06-2012 23:00, 06-06-2012 07:00

2, 06-06-2012, 06-06-2012 07:00, 06-06-2012 17:00

3, 06-06-2012, 06-06-2012 17:00, 06-06-2012 23:00

];

Re: Intervalmatch not working well

Sun, 02 Sep 2012 16:03:05 GMT

Yhanks Rakesh

For 6-6-2012 the result is ok but still there is a problem for 5-6-2012 as this gives a null value for 'DatumTijd'?

Re: Intervalmatch not working well

disqr_rm — Sun, 02 Sep 2012 21:14:54 GMT

Your data is not in the time range for 05-06-2012. Use range below and it works. Changes are in bold.

Schema:
LOAD Name, Datum, timestamp#(From, 'DD-MM-YYYY hh:mm') as From, timestamp#(Until, 'DD-MM-YYYY hh:mm') as Until;

LOAD * INLINE [
    Name, Datum, From, Until
    1, 05-06-2012, 05-06-2012 00:00, 06-06-2012 06:59
    2, 06-06-2012, 06-06-2012 07:00, 06-06-2012 19:59
    3, 06-06-2012, 06-06-2012 17:00, 06-06-2012 23:00
];

INNER Join (Schema)
IntervalMatch (DatumTijd)
Load
From,
Until
Resident Schema;

Re: Intervalmatch not working well

Mon, 03 Sep 2012 06:11:36 GMT

Hi Rakesh

Thanks but also not working

due to the inner join days and Datum and DatumTijd are not matching anymore

made some adjustments but still having problems

Schema:

LOAD Name, Datum, timestamp#(From, 'DD-MM-YYYY hh:mm') as From, timestamp#(Until, 'DD-MM-YYYY hh:mm') as Until;

LOAD * INLINE [

Name, Datum, From, Until

1, 05-06-2012, 05-06-2012 00:00, 06-06-2012 07:00

2, 06-06-2012, 06-06-2012 05:00, 06-06-2012 20:00

3, 06-06-2012, 06-06-2012 17:00, 06-06-2012 23:00

];

left Join (Calendar)

IntervalMatch (DatumTijd)

Load

From,

Until

Resident Schema;