How to join two tables based on interval match

Fri, 26 Jun 2015 06:59:27 GMT

Dear All,

I have two tables A & B having weight information of two different reader as follows

ID FromDateTime ToDateTime Weight

-----------------------------------------------------------------------------------------------------------

RJ09GA3510 15/06/2015 10:15:00 15/06/2015 10:30:00 50

RJ09GA3510 15/06/2015 13:05:00 15/06/2015 13:30:00 59

GJ05TA6152 17/06/2015 17:10:00 17/06/2015 17:35:00 65

-----------------------------------------------------------------------------------------------------------

ID DateTime Weight

-----------------------------------------------------------------------------------------------------------

RJ09GA3510 15/06/2015 10:18:00 50.5

RJ09GA3510 15/06/2015 13:08:00 59.3

GJ05TA6152 17/06/2015 17:19:00 61.2

Now, there is a requirement to get variance of weight from these two tables, and data need to be shown in following way

ID A.FromDateTime B.DateTime A.Weight B.Weight Difference(B-A)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

RJ09GA3510 15/06/2015 10:15:00 15/06/2015 10:18:00 50 50.5 .5

RJ09GA3510 15/06/2015 13:05:00 15/06/2015 13:08:00 59 59.3 .3

GJ05TA6152 17/06/2015 17:10:00 17/06/2015 17:19:00 65 62.2 2.8

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Please suggestions to join these two tables to get desired output as per requiredment.

Thanks in Advance

Regards

Hari Prasad

Re: How to join two tables based on interval match

hic — Fri, 26 Jun 2015 09:24:19 GMT

You don't need to join. Just load the tables using a standard extended intervalmatch:

Intervals:
Load *, ID &'|'& FromDateTime &'|'& ToDateTime as RangeKey Inline
[ID, FromDateTime,ToDateTime,WeightA
RJ09GA3510, 15/06/2015 10:15:00,15/06/2015 10:30:00,50
RJ09GA3510, 15/06/2015 13:05:00,15/06/2015 13:30:00,59
GJ05TA6152, 17/06/2015 17:10:00,17/06/2015 17:35:00,65];

Weights:
Load *, ID &'|'& DateTime as DateTimeKey Inline
[ID, DateTime, WeightB
RJ09GA3510, 15/06/2015 10:18:00,50.5
RJ09GA3510, 15/06/2015 13:08:00,59.3
GJ05TA6152, 17/06/2015 17:19:00,61.2];

tmpIntervalMatch:
IntervalMatch (DateTime,ID)
Load FromDateTime,ToDateTime,ID Resident Intervals;

IntervalMatch:
Load
ID &'|'& FromDateTime &'|'& ToDateTime as RangeKey,
ID &'|'& DateTime as DateTimeKey
Resident tmpIntervalMatch;

Drop Table tmpIntervalMatch;
Drop Field ID From Intervals;

and you will be able to do what you want in a chart:

HIC

topic Re: How to join two tables based on interval match in QlikView

How to join two tables based on interval match

Re: How to join two tables based on interval match