intervalmatch on client works, but not as a task?

[sebastianfaust]

hi all,

I have a strange problem, at least it seems strange to me.

However, if i run this script on my client it runs through perfect:

FST_DATEI_FZH_SERVICEFALL_EVENT_PROGR1:
load
Text(FGNR7) as FGNR7_FST_DATEI_PROGR,
Text(FGNR17) as FGNR17_FST_DATEI_PROGR,
timestamp(DIAG_DATETIME) as DIAG_DATETIME_FST_DATEI_PROGR,
time(DIAG_DATETIME - (10/86400)) as DIAG_DATETIME_FST_DATEI_PROGR_UG,
time(DIAG_DATETIME + (10/86400)) as DIAG_DATETIME_FST_DATEI_PROGR_OG,
timestamp(ERRTIME) as ERRTIME_FST_DATEI_PROGR,
Year(ERRTIME)& '-' &num(Month(ERRTIME),00) as ERRTIME_JAHR_MONAT_FST_DATEI_PROGR,
DATEINAME as DATEINAME_FST_DATEI_PROGR,
1 as ZAEHL_PROGR_ABG_FST_DATEI_PROGR
from
$(vPfad)FST_DATEI_PROGR.qvd (qvd)
where exists (FGNR17_DIM_FAHRZEUG, FGNR17)
;

FZH_SERVICEFALL_EVENT_PROGR:
load
Text(FGNR7_FZA) as FGNR7_FST_DATEI_PROGR,
Text(FGNR17_FZA) as FGNR17_FST_DATEI_PROGR,
Time(Frac(timestamp(REFERENZ_DT))) as REFERENZ_DT_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG,
num(EVENT_ID_EVENT) as EVENT_ID_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG,
num(EVENT_ID_PROGR) as EVENT_ID_FZH_EV_PROGRAMMIERUNG_ABG,
STATUS as STATUS_FZH_EV_PROGRAMMIERUNG_ABG,
if (isnull(EVENT_ID_EVENT),0,1) as ZAEHL_PROGR_ABG_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG_TEMP,
if (isnull(EVENT_ID_PROGR),0,1) as ZAEHL_PROGR_ABG_FZH_EV_PROGRAMMIERUNG_ABG_TEMP,
timestamp#(mid(timestamp(INSERT_DATE_FZA,'DD.MM.YYYY hh:mm:ss'),1,16),'DD.MM.YYYY hh:mm') as INSERT_DATE_FZH_EV_PROGRAMMIERUNG_ABG
from
$(vPfad)FZH_SERVICEFALL_EVENT_PROGR.qvd (qvd)
;

inner join INTERVALMATCH (REFERENZ_DT_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG, FGNR17_FST_DATEI_PROGR)
load DIAG_DATETIME_FST_DATEI_PROGR_UG, DIAG_DATETIME_FST_DATEI_PROGR_OG, FGNR7_FST_DATEI_PROGR, FGNR17_FST_DATEI_PROGR resident FST_DATEI_FZH_SERVICEFALL_EVENT_PROGR1;

if I create a task i get the following error:

inner join INTERVALMATCH (REFERENZ_DT_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG, FGNR17_FST_DATEI_PROGR)
 load DIAG_DATETIME_FST_DATEI_PROGR_UG, DIAG_DATETIME_FST_DATEI_PROGR_OG, FGNR7_FST_DATEI_PROGR, FGNR17_FST_DATEI_PROGR resident FST_DATEI_FZH_SERVICEFALL_EVENT_PROGR1

 4 fields found: DIAG_DATETIME_FST_DATEI_PROGR_UG, DIAG_DATETIME_FST_DATEI_PROGR_OG, FGNR7_FST_DATEI_PROGR, FGNR17_FST_DATEI_PROGR,
 Error: Interval Fields List not matching number of fields.

am I overseeing any obvious?

thanks!

[FakeJupiter]

I don't have a reply to this, I'm just looking for a solution or explanation for this error too:

Interval Fields List not matching number of fields.

What is that? This suddenly started appearing in a QV document I'm running every month, made no changes whatsoever. I even added floor() functions to interval and matching field dates, so that there are no format integrity issues.

[marcus_sommer]

I couldn't provide an answer only some hints. Within the help to the intervalmatch are remarks regarding the included data - of course the interval-fields must be numeric but also that there could be unexpected results if there are NULL's which should be checked/replaced in beforehand.

Therefore I suggest to make a quick test with a smaller subset of the data - with any where-conditions which surely excludes possible NULL's and if this worked to extend the dataset to find the causing records (maybe there is an issue with the data-quality) and/or to implement logics to replace the NULL's with real values.

Beside this you could consider to replace the inbuilt intervalmatch-feature with an internal while-loop like:

load [from]+ iterno() - 1 as Date, * from Source
while [from] + iterno() - 1 <= to;

which is my preferred approach for such tasks.

- Marcus

[andykrst]

u need to put , key field in intervalmatch to the list of loaded field.

and only load interval start- end field and key field needed in load statement after intervalmatch.

exam :

inner join INTERVALMATCH (REFERENZ_DT_FZH_SERVICEFALL_EVENT_TYP_PROGR_ABG, FGNR17_FST_DATEI_PROGR)
 load DIAG_DATETIME_FST_DATEI_PROGR_UG,

DIAG_DATETIME_FST_DATEI_PROGR_OG,

FGNR7_FST_DATEI_PROGR

resident FST_DATEI_FZH_SERVICEFALL_EVENT_PROGR1