Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
draghici1109
Contributor III
Contributor III

InnerJoin Intervalmatch fetches 0 lines

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?

 

Alexandru Draghici
BICC at Komm.ONE
Labels (1)
  • Other

8 Replies
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
Contributor III
Contributor III
Author

draghici1109_0-1623326367321.png

 

Alexandru Draghici
BICC at Komm.ONE
sunny_talwar

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

draghici1109
Contributor III
Contributor III
Author

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

Alexandru Draghici
BICC at Komm.ONE
sunny_talwar

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

draghici1109
Contributor III
Contributor III
Author

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

Alexandru Draghici
BICC at Komm.ONE
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
Contributor III
Contributor III
Author

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

Alexandru Draghici
BICC at Komm.ONE