Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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.
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