left join two tables from different source using time including 5minutes error

[JPbeginner]

 hi community,

i would lIke to join  tableA from  sourceA and tableB from sourceB, using ID column and time column, but those  time record (time stamp from different machines) is not exactly same, a few minutes error included.

so i would like to join them like:

1, tableA.itemID = tableB .targetID

AND

2,absolute value of the time record difference between tableA and B is smaller than 5 minutes

i tried like below but didn't go well...

lib connect to sourceA;

load *

from tableA;

lib connect to sourceB;

load*

from tableB 

left join (tableA)

on ......

is this possible?

[PrashantSangle]

if you explain your query with data then it will more clear to us.

[marcus_sommer]

A join in Qlik is performed against all native fields with the same field-name. This means the first ID key is simple - just renaming the fields that they are identically. The second Time key could not directly solved - at least not if time-values from both sources should be forward and backward evaluated against each other.

If this is really necessary you would need a multiple steps approach with multiple joins or probably better multiple mappings or rather more expensive to join at first without the second key and evaluating all results within a sorted resident-load with interrecord-functions against each other.

Before going so far I suggest to consider just to round the time-values to 5 minutes equally on both sides and using the this value as second key.

[JPbeginner]

Hi, here is my query and data. 

________

Table1:

LIB CONNECT TO 'Machine';

LOAD MecaID,ProgramCD, TargetID,ADDDTM;

SQL Select MecaID, ProgramCD, TargetID, AddDTM 

FROM Machine

Where AddDTM > ADD_MONTH(SYSDATE,-24)

;

Table2:

LIB CONNECT TO 'Inspection';

LOAD ItemID as TargetID, Height,  InspDTM;

SQL Select ItemID, Height,  InspDTM 

FROM Inspection

;

JoinedTable

LOAD*

RESIDENT Table1;

LEFT JOIN (Table2)

    LOAD*

    RESIDENT Table2

    Where (Table1.AddDTM-Table2.InspDTM) < 1/24/12;

______

I always get error which sais "filed 'Table1.AddDTM' not found",,,

my data looks like below, i would like to connect those tables and analyze how the height of SN123456 changes by ProgramCD. however 'inspection' table do not have which ProgramCD is applied, so i'm trying to join those tables by "small time difference". 

Machine

MecaID	ProgramCD	TargetID	AddDTM
MAchineA	Step1	SN123456	2021/04/24 2:06:24
MachineB	Finish	SN999999	2022/10/07 3:39:17
MAchineA	Step2	SN123456	2021/05/24 2:06:24

Inspection

ItemID	Height	InspDTM
SN123456	400	2021/04/24 2:06:35
SN123456	200	2021/05/24 2:04:38

[Kushal_Chawda]

@JPbeginner  what is expected output based on above example?

[Kushal_Chawda · Accepted solution]

@JPbeginner  you cannot reference field from different table while taking resident of table. Resident refers to single table and only fields from that table can be used in where conditions. You need to use intervalmatch here.

Note:  Make Sure that your both Date field is in actual timestamp format. If not first you need to convert it to actual timestamp format using timestamp# function then you can do something like below

Machine:
LOAD
    MecaID,
    ProgramCD,
    TargetID,
    AddDTM
FROM Machine

Inspection:
LOAD
    ItemID as TargetID,
    Height,
    InspDTM,
    timestamp(InspDTM - Time#('05:00','mm:ss')) as InspDTM_Start,
    timestamp(InspDTM + Time#('05:00','mm:ss')) as InspDTM_End
FROM Inspection;

Inner join 
IntervalMatch ( AddDTM, TargetID )
LOAD InspDTM_Start, InspDTM_End, TargetID
Resident Inspection;

drop fields InspDTM_Start,InspDTM_End;