topic join in QlikView

join

syukyo_zhu — Wed, 24 Sep 2014 13:25:38 GMT

Hi,

I have the following two tables:

table1: table2:

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

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

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

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

Re: join

JonnyPoole — Wed, 24 Sep 2014 13:32:03 GMT

In your example the table2 timestamp is greater than the table1 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 ?

Re: join

syukyo_zhu — Wed, 24 Sep 2014 13:35:51 GMT

yes i did an mistake

Re: join

JonnyPoole — Wed, 24 Sep 2014 13:37:44 GMT

What would be the correct result ?

This:

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

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

or This:

table2.timestamp <= table1.timestamp

Re: join

syukyo_zhu — Wed, 24 Sep 2014 13:39:32 GMT

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

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

Re: join

Gysbert_Wassenaar — Wed, 24 Sep 2014 13:43:02 GMT

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

Re: join

JonnyPoole — Wed, 24 Sep 2014 13:44:58 GMT

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 ?

Re: join

syukyo_zhu — Wed, 24 Sep 2014 14:10:48 GMT

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.

Re: join

syukyo_zhu — Wed, 24 Sep 2014 14:18:27 GMT

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,