Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Hi,

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

Table1:

Entry_DateEntry_No_StartEntry_No_End
2010-01-011000110010
2010-01-101001110023

Table2:

Document_NoEntry_No
DOC0000110001
DOC0000210011
DOC0000310021

Expected Output

Document_NoEntry_DateEntry_No
DOC000012010-01-0110001
DOC000022010-01-0110011
DOC000032010-01-1010021

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

5 Replies

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

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
Esteemed Contributor

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

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

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

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

Not applicable

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

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.

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

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