Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i had a data in excel sheet, Event and Trip tables, i need a specified fields in to the the trip table,
Event
Date | Vehicle no | event end date time | event discriptions | Total occurances | Event duration |
Trip
Date | Vehicle no | Driver name | Trip start date and time | Trip end date and time | Distance travel |
i need to get like this in a single table, without using unique key and not to be done in front end..
Date | Vehicle no | Driver name | Trip start date and time | Trip end date and time | Distance travel |
event end date time, Event duration
i used, join, concatenate, link tables also but cant find the solution...
can help any one..
Can you please add a "desired results" tab to your spreadsheet to illustrate your desired outcome?
maybe this?
Events:
LOAD Date,
[Vehicle no],
[event end date time],
[event discriptions],
[Total occurances],
[Event duration]
FROM
Book1.xlsx
(ooxml, embedded labels, table is Event);
Trips:
LOAD Date,
[Vehicle no],
[Driver name],
[Trip start date and time],
[Trip end date and time],
[Distance travel]
FROM
Book1.xlsx
(ooxml, embedded labels, table is trips);
inner join IntervalMatch ([event end date time], Date, [Vehicle no])
LOAD [Trip start date and time], [Trip end date and time], Date, [Vehicle no] Resident Trips;
Hi,
Please check this example.
Example 1:
In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.
EventLog:
LOAD * Inline [
Time, Event, Comment
00:00, 0, Start of shift 1
01:18, 1, Line stop
02:23, 2, Line restart 50%
04:15, 3, Line speed 100%
08:00, 4, Start of shift 2
11:43, 5, End of production
];
OrderLog:
LOAD * INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];
//Link the field Time to the time intervals defined by the fields Start and End.
Inner Join IntervalMatch ( Time )
LOAD Start, End
Resident OrderLog;
Result:
The table OrderLog contains now an additional column: Time. The number of records is also expanded.
Time | Start | End | Order |
---|---|---|---|
00:00 | - | - | - |
01:18 | 01:00 | 03:35 | A |
02:23 | 01:00 | 03:35 | A |
04:15 | 02:30 | 07:58 | B |
04:15 | 03:04 | 10:27 | C |
08:00 | 03:04 | 10:27 | C |
08:00 | 07:23 | 11:43 | D |
11:43 | 07:23 | 11:43 | D |
Example 2: (using keyfield)
Same example than above, adding ProductionLine as a key field.
EventLog:
LOAD * Inline [
Time, Event, Comment, ProductionLine
00:00, 0, Start of shift 1, P1
01:00, 0, Start of shift 1, P2
01:18, 1, Line stop, P1
02:23, 2, Line restart 50%, P1
04:15, 3, Line speed 100%, P1
08:00, 4, Start of shift 2, P1
09:00, 4, Start of shift 2, P2
11:43, 5, End of production, P1
11:43, 5, End of production, P2
];
OrderLog:
LOAD * INLINE [
Start, End, Order, ProductionLine
01:00, 03:35, A, P1
02:30, 07:58, B, P1
03:04, 10:27, C, P1
07:23, 11:43, D, P2
];
//Link the field Time to the time intervals defined by the fields Start and End and match the values
// to the key ProductionLine.
Inner Join
IntervalMatch ( Time, ProductionLine )
LOAD Start, End, ProductionLine
Resident OrderLog;
Result:
A table box could now be created as below:
ProductionLine | Time | Event | Comment | Order | Start | End |
---|---|---|---|---|---|---|
P1 | 00:00 | 0 | Start of shift 1 | - | - | - |
P2 | 01:00 | 0 | Start of shift 1 | - | - | - |
P1 | 01:18 | 1 | Line stop | A | 01:00 | 03:35 |
P1 | 02:23 | 2 | Line restart 50% | A | 01:00 | 03:35 |
P1 | 04:15 | 3 | Line speed 100% | B | 02:30 | 07:58 |
P1 | 04:15 | 3 | Line speed 100% | C | 03:04 | 10:27 |
P1 | 08:00 | 4 | Start of shift 2 | C | 03:04 | 10:27 |
P2 | 09:00 | 4 | Start of shift 2 | D | 07:23 | 11:43 |
P1 | 11:43 | 5 | End of production | - | - | - |
P2 | 11:43 | 5 | End of production | D | 07:23 | 11:43 |
Hi,
Please check this also.
Test:
LOAD * Inline [
from, to, area
1, 999, Delhi
1000, 2000, Nagaland
2021, 3455, Punjab
];
Data:
LOAD * Inline [
Data, area
555, Delhi
100000, orissa
1111, Nagaland
2040, Punjab
2045, Punjab
];
Left join(Data)
Intervalmatch(Data)
LOAD from, to
RESIDENT Test;
Left join (Data)
LOAD * resident Test;
drop table Test;
Hi,
Please check this also.
A common problem in business intelligence is when you want to link a number to a range. It could be that you have a date in one table and an interval – a “From” date and a “To” date – in another table, and you want to link the two tables. In SQL, you would probably join them using a BETWEEN clause in the comparison.
Hi Samarender,
Try below solution.
Event:
LOAD Date,
[Vehicle no],
[event end date time],
[event discriptions],
[Total occurances],
[Event duration]
FROM
(ooxml, embedded labels, table is Event);
Inner Join
Trips:
LOAD Date,
[Vehicle no],
[Driver name],
[Trip start date and time],
[Trip end date and time],
[Distance travel]
FROM
(ooxml, embedded labels, table is trips);
Result:
LOAD Date,
[Vehicle no],
[Driver name],
[Trip start date and time],
[Trip end date and time],
[Distance travel],
[event end date time],
[Event duration]
Resident Event;
Drop Table Event;
Hi Samarender,
Please find the below
Events:
LOAD
Date,
[Vehicle no],
[event end date time] ,
[event discriptions],
[Total occurances],
[Event duration]
FROM
[..\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Event);
Trips:
LOAD Date,
[Vehicle no],
[Driver name],
[Trip start date and time] ,
[Trip end date and time],
[Distance travel]
FROM
[..\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is trips);
Inner Join
IntervalMatch:
IntervalMatch([event end date time])
LOAD Distinct [Trip start date and time],[Trip end date and time] Resident Trips;
and the output you will get is