Join tables on Qlik using date field (similar SQL statement provided)

Question

I'm trying to join in Qlik two similar tables using a combination of various fields -other than keys- plus a date check. An SQL statement to do the job could be the following.

SELECT
    ID,
    Field_1,
    Field_2,
    Field_3,
    Rec_Date
INTO
    bTable
FROM
    aTable


SELECT
    cTable.ID AS Master_ID,
    bTable.Field_1,
    bTable.Field_2,
    bTable.Field_3,
    bTable.Rec_Date
INTO
    dTable  
FROM
    bTable  JOIN bTable AS cTable ON 
            bTable.Field_1 = cTable.Field_1 AND
            bTable.Field_1 = cTable.Field_1 AND
            bTable.Field_1 = cTable.Field_1 AND
            bTable.Rec_Date > cTable.Rec_Date

I'm trying do it in Qlik. Any help welcome

Answer 1

assumng aTable is already avialable, you can use something similar to the below:

btable: 
Load
ID,
Field_1,
Field_2,
Field_3,
Rec_Date
Resident
aTable;

dTable:     
Qualify *;
Load   *
Resident bTable;  
INNER JOIN 
cTable:
Load * 
Resident cTable;  
   
   
etable:
unqualify *;
load cTable.ID AS Master_ID,
        bTable.Field_1,
        bTable.Field_2,
        bTable.Field_3,
        bTable.Rec_Date 
resident dTable 
where
bTable.Rec_Date > cTable.Rec_Date;

drop table cTable;
drop table dTable;

The key here is using Qualify so that the 2 tables are not joined automatically on each field. You can then use Unqalify in the last table and rename the fields as needed.

This can help further: https://community.qlik.com/t5/QlikView-Documents/Beginners-Task-Series-Task6-Self-Join/ta-p/1488728