Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
@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;
if you explain your query with data then it will more clear to us.
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.
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 |
@JPbeginner what is expected output based on above example?
@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;