InnerJoin Intervalmatch fetches 0 lines

[draghici1109]

I am using the following code to generate a mapping table:

BGRAD:
LOAD Distinct
     PERNR,
     BSGRD,
     Num#(BEGDA)     AS BStart,
     Num#(ENDDA)     AS BEnd
FROM [..\DATA\00-08000000-90-100-PA0008.qvd](qvd);

Inner Join IntervalMatch (DATUM_SAP, PERNR)
LOAD Distinct
     BStart,
     BEnd,
     PERNR
Resident BGRAD;

mBGRAD:
Mapping
LOAD Distinct
     PERNR & '-' & DATUM_SAP AS %key,
     BSGRD
Resident BGRAD;

for DATUM_SAP I use this code:

For y=Year(Today()) to (Year(Today())+5)
For m=1 to 12

CAL:
LOAD
MakeDate($(y),$(m),1) AS DATUM_REAL,
Num(MakeDate($(y),$(m),1)) AS DATUM_SAP
AutoGenerate(1);

Next
Next

And yet, as I execute the script, it fetch 0 lines, as the log file displays:

BGRAD << 00-08000000-90-100-PA0008 25.063 Lines fetched
BGRAD 0 Lines fetched
mBGRAD << BGRAD 0 Lines fetched

Any idea what could be the cause of this behavior/result?

[sunny_talwar]

Can you share how your data looks within 00-08000000-90-100-PA0008.qvd? I think it might have to do with BStart and BEnd not getting read as dual fields with an underlying numeric value.

[draghici1109]

[sunny_talwar]

Can you do just Join instead of Inner Join to see where the mismatch is happening?

[draghici1109]

I used only (outer) join insted of inner join and the log file shows 

BGRAD << 00-08000000-90-100-PA0008 25.063 Lines fetched
BGRAD 0 Lines fetched
mBGRAD << BGRAD 25.062 Lines fetched

[sunny_talwar]

Where is DATUM_SAP coming from? I am not seeing any script for that above?

[draghici1109]

this is the code:

For y=Year(Today()) to (Year(Today())+5)
For m=1 to 12

CAL:
LOAD
MakeDate($(y),$(m),1) AS DATUM_REAL,
Num(MakeDate($(y),$(m),1)) AS DATUM_SAP
AutoGenerate(1);

Next
Next

[sunny_talwar]

Oh ya, I see it now in the initial post. Sorry I didn't realize it was already there. You might already be doing this, but are you creating this table before you do IntervalMatch, right?

[draghici1109]

yes at the very beginning... and actually it works with previous similar ApplyMaps ... only this one is "disobedient" 😁

see here an excerpt from the log file:

CAL << AUTOGENERATE(1) 70 Lines fetched
CAL << AUTOGENERATE(1) 71 Lines fetched
CAL << AUTOGENERATE(1) 72 Lines fetched
CAL-1 << 00-08000000-90-100-T528T (row-based qvd optimized) 8.752 Lines fetched
HRP1001 << 00-08000000-90-100-HRP1001 8.569 Lines fetched
HRP1001 311.680 Lines fetched
BASE << HRP1001 311.680 Lines fetched
mPER << BASE 123.163 Lines fetched
KST << 00-08000000-90-100-HRP1001 534 Lines fetched
KST 10.362 Lines fetched
mKST << KST 10.362 Lines fetched
BGRAD << 00-08000000-90-100-PA0008 25.063 Lines fetched
BGRAD 0 Lines fetched
mBGRAD << BGRAD 0 Lines fetched