how to merge two tables

[Anonymous]

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..

[wdchristensen]

Can you please add a "desired results" tab to your spreadsheet to illustrate your desired outcome?

[maxgro]

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;

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/IntervalMatch_(Extended_...

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

[balabhaskarqlik]

Check these:

https://community.qlik.com/thread/271093

https://community.qlik.com/thread/284752

[qlikviewwizard]

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

[qlikviewwizard]

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;

Interval Match or Inline load

[qlikviewwizard]

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.

IntervalMatch

[Anonymous]

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;

[Anonymous]

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