IntervalMatch help needed

[#1]

Hello,

I'm trying to get the information to the corresponding shift team to a production batch with a given start-time.

The start-time/end-time is loaded from the qvd (oracle data) at the beginning of the script. Several QVDs follows, they are not cited here. At the end of the script external data sources are linked. 'Date' as start of the 'Shift' from an xls file should be linked to the batch (start) from the qvd data.

Either I go a synthetic key by working with the join function or I can't get a link to the shift table...

I loose my time over finding a solution in this issue.

Could anybody help me?

Directory;

LOAD 

HEADERS_ENDTIME,

HEADERS_MATERIAL,

HEADERS_STARTTIME,

FROM

data\CPDM_XX.QVD

(qvd)

where

HEADERS_UNIT = 'EX B143';

//..REMARK: OTHER QVD-Loads...

Directory;

Shift:

Load

              timestamp(Date) as Date,

              Shift

FROM

Date_LetterShoot.xls

(biff, embedded labels, table is Shift$);

Intervalmatch(Date)

load HEADERS_ENDTIME, HEADERS_STARTTIME from data\CPDM_XX.QVD (qvd);

[martin59 · Accepted solution]

Hi,

You have to do something like this :

Directory;
Shift:
Load 
              timestamp(Date) as Date,
              Shift
FROM
Date_LetterShoot.xls
(biff, embedded labels, table is Shift$);
Intervalmatch(Date)
LOAD 
HEADERS_STARTTIME,
HEADERS_ENDTIME,
HEADERS_MATERIAL
 FROM data\CPDM_XX.QVD (qvd)
where HEADERS_UNIT = 'EX B143';

After that, you have to remove syn keys et review your data model

Look at the attachement for an exemple.

Hope that helps you

Martin

[#3]

Martin, je m'excuse pour n'avoir pas encore repondu!! Merci, merci pour votre aide!

Your attached qvw contained the solution!  Furthermore, I had to inverse S and E (!!) in the intervalmatch-function.

Find below my modified skripting, for information only.

Directory;

LOAD 

HEADERS_ENDTIME,

HEADERS_MATERIAL,

HEADERS_STARTTIME,

autonumber(HEADERS_ENDTIME&'-'&HEADERS_STARTTIME) as id_interval

FROM

data\CPDM_XX.QVD

(qvd)

where

HEADERS_UNIT = 'EX B143';

//..REMARK: OTHER QVD-Loads...

Time:

Load

HEADERS_ENDTIME as E,

HEADERS_STARTTIME as S,

autonumber(HEADERS_ENDTIME&'-'&HEADERS_STARTTIME) as id_interval

from

data\CPDM_XX.QVD (qvd);

Shift:

Load

              timestamp(Date) as Date,

              Shift

FROM

Date_LetterShoot.xls

(biff, embedded labels, table is Shift$);

Intervalmatch(Date)

load S, E resident time;