Joining two tables where one field in second table is within the range in two fields in the first table.

[#1]

Hi,

Please help, I would like to join this two table:

Table1:

Entry_Date	Entry_No_Start	Entry_No_End
2010-01-01	10001	10010
2010-01-10	10011	10023

Table2:

Document_No	Entry_No
DOC00001	10001
DOC00002	10011
DOC00003	10021

Expected Output

Document_No	Entry_Date	Entry_No
DOC00001	2010-01-01	10001
DOC00002	2010-01-01	10011
DOC00003	2010-01-10	10021

Entry_No is within the rainge in two fields in the first table.

Thanks in advance,

Message was edited by: malatiit - Correction on Table 2

[its_anandrjs]

Hi,

You have to check with the condition like

If ( Entry_No_Start >= Entry_No  and Entry_No_End <= Entry_No  , Entry_Date) in Expected output table

Rgds

Anand

[SunilChauhan]

i belive you have wrong fieeldname .

in second table 1st field might be entrydate in place of Entry_No.

if so lease correct those.

or else i am not understanding

[its_anandrjs]

Hi,

See the attached sample file where i use a straight table and you have to join it with Entry date field and i use Doc table to explain what you have if you have data like that and then chek the Entry_No in table.

with expression as condition

Dimension:- Document_No and Entry_No

Expression:-If ( Entry_No_Start <= Doc.Entry_No  and Entry_No_End >= Doc.Entry_No  , Entry_Date)

See the solution sheet

HTH

Rgds

Anand

[#5]

Hi, Thanks for you help but I haven't really use the "qualify" and the other one, what can you say about this code?:

GLREGISTER:
LOAD

     ENTRYNO,
     DOCNO
FROM RESIDENTJOIN.xlsx (ooxml, embedded labels, table is GLENTRY);

JOIN LOAD

     ENTRYDATE,
     ENTRYSTART,
     ENTRYEND
FROM RESIDENTJOIN.xlsx (ooxml, embedded labels, table is GLREGISTER);
STORE * FROM GLREGISTER INTO GLREGISTER.QVD;
DROP TABLE GLREGISTER;

LOAD

     ENTRYNO,
     DOCNO,
     ENTRYDATE,
     ENTRYSTART,
     ENTRYEND
FROM GLREGISTER.QVD (QVD) WHERE ENTRYNO>=ENTRYSTART AND ENTRYNO<=ENTRYEND;

The code above is actually working fine already, but when it comes to handling millions of records, the records will multiply several times since no fields are the same.

[its_anandrjs]

Hi,

Yes code is run correctly but we are checking the where condition like 

WHERE ENTRYNO>=ENTRYSTART AND ENTRYNO<=ENTRYEND in any expression you are do one thing also if you add a calcualated column in table when you load from QVD like

If ( Entry_No_Start <= Doc.Entry_No  and Entry_No_End >= Doc.Entry_No  , Entry_Date)  as EntryDetail

it will also works

HTH

Rgds

Anand