join

[syukyo_zhu]

Hi,

I have the following two tables:

table1: table2:

     id | name | timestamp                                              id | name | value | timestamp

-------- | ------------ | ----------------------                               ------ --- | ------------- | ------------- | ------------------ ---

     1 | Name1 | 2013-10-21 10:23                                         1 | Name 1 | 2.01 | 2013-09-20 10:24

     2 | Name4 | 201-10-21 10:25                                           2 | Name 1 | 1.93 | 2013-10-21 10:33

     3 | Name2 | 2013-10-21 10:27                                         3 | Name1 | 0.05 | 2013-10-22 10:00

     4 | Name3 | 2013-10-21 10:28                                         4 | Name1| 3.50 | 2013-10-23 10:25

I'd like to do join  two tables with the following conditions:

table1.name = table2.name (it's OK)

table2.timestamp and is the nearest lower compared to table1.timestamp

So I would like to get a table like this

     id | name | timestamp | id | name | rate | v_timestamp

--------|------------|----------------------|---------|-------------|-------------|---------------------

     1 | Name1 | 2013-10-21 10:23 | 2 | Name 1 | 2.01 | 2013-09-20 10:24 .

I know how do it in sql, but it will take too long.

like:

left join table1.name = table2.name and table1.timestamp >= table2.timestamp.

someone know how can i do it in qlikview with deux qvd

thanks

[JonnyPoole]

In your example the table2 timestamp is greater than the table1 timestamp

     id | name | timestamp | id | name | rate | v_timestamp

--------|------------|----------------------|---------|-------------|-------------|---------------------

     1 | Name1 | 2013-10-21 10:23 | 2 | Name 1 | 1.93 | 2013-10-21 10:33 .

2013-10-21 10:33   >= 2013-10-21 10:23 

not

table2.timestamp <= table1.timestamp

do you have a mistake in the explanation ?

[syukyo_zhu]

yes i did an mistake

[JonnyPoole]

What would be the correct result ? 

This:

     id | name | timestamp | id | name | rate | v_timestamp

--------|------------|----------------------|---------|-------------|-------------|---------------------

     1 | Name1 | 2013-10-21 10:23 | 2 | Name 1 | 1.93 | 2013-10-21 10:33 .

or This:

table2.timestamp <= table1.timestamp

[syukyo_zhu]

     id | name | timestamp | id | name | rate | v_timestamp

--------|------------|----------------------|---------|-------------|-------------|---------------------

     1 | Name1 | 2013-10-21 10:23 | 2 | Name 1 | 1.93 | 2013-10-21 10:33 .

[Gysbert_Wassenaar · Accepted solution]

In Qlikview you'd do something like this:

Temp:

LOAD id as id_1, name, timestamp#(timestamp,'YYYY-MM-DD hh:mm') as timestamp_1

FROM ...

join

LOAD id as id_2, name, value, timestamp#(timestamp,'YYYY-MM-DD hh:mm') as timestamp_2

FROM ...

Result:

NoConcatenate

LOAD * resident Temp

WHERE timestamp_1 >= timestamp_2;

drop table Temp;

But with your example data that results in an empty table since each timestamp in the second table is larger than the timestamp value for name1 in the first table

[JonnyPoole]

So if i understand, you want to join when the name fields are the same and then amongst the records where the name is the same, retrieve the record that has the greatest (or max) timestamp ?  Does that max timestamp have to be greater than or equal to the timestamp from the record in the first table or can it be any timestamp as long as its the maximum  ?

[syukyo_zhu]

Hi, i have juste changed my request. i think you will understand well.

i would like find the record in my second table: the max timestamp for each name but timestamp finded should be lower than the timestampe for the same name in my first table.

[syukyo_zhu]

Hi,

it works well.

at first, j'am afraid about volume(so my first table do 1 million records) et second do 2 million records.

Juste at the end, i need do max(timestamp_2) .. group by name to get the max.

thanks,