topic Re: InnerJoin Intervalmatch fetches 0 lines in App Development

InnerJoin Intervalmatch fetches 0 lines

draghici1109 — Tue, 30 Nov 2021 18:17:33 GMT

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?

Re: InnerJoin Intervalmatch fetches 0 lines

sunny_talwar — Thu, 10 Jun 2021 11:56:34 GMT

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.

Re: InnerJoin Intervalmatch fetches 0 lines

draghici1109 — Thu, 10 Jun 2021 12:00:03 GMT

Re: InnerJoin Intervalmatch fetches 0 lines

sunny_talwar — Thu, 10 Jun 2021 13:01:52 GMT

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

Re: InnerJoin Intervalmatch fetches 0 lines

draghici1109 — Thu, 10 Jun 2021 15:03:44 GMT

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

Re: InnerJoin Intervalmatch fetches 0 lines

sunny_talwar — Thu, 10 Jun 2021 15:32:03 GMT

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

Re: InnerJoin Intervalmatch fetches 0 lines

draghici1109 — Fri, 11 Jun 2021 06:13:39 GMT

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

Re: InnerJoin Intervalmatch fetches 0 lines

sunny_talwar — Fri, 11 Jun 2021 12:56:07 GMT

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?

Re: InnerJoin Intervalmatch fetches 0 lines

draghici1109 — Fri, 11 Jun 2021 13:01:26 GMT

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