Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I have two tables A & B having weight information of two different reader as follows
A:
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
-----------------------------------------------------------------------------------------------------------
B:
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
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