topic Re: Join on datetime in QlikView

Join on datetime

Wed, 25 Nov 2020 16:16:04 GMT

Hi everybody,

After 1 day of research, i resign to post on forum...

I have two data set of server logs

Data 1:

Header 1	Header 2	Header 3
2016-03-16 08:05:12	Action 1	Data 1

I want to match with

Header 1	Header 2
2016-03-16 08:00:00	John DOE

All combination i had tried like timestamp transformation didn't worked,

Someone can help me ?

Thx

Re: Join on datetime

sunny_talwar — Fri, 18 Mar 2016 18:24:21 GMT

Does the match need to be at the hour level? or Date level?

Re: Join on datetime

MarcoWedel — Sat, 19 Mar 2016 01:10:37 GMT

you have to define in which cases different timestamps should match ...

regards

Marco

Re: Join on datetime

Sat, 19 Mar 2016 14:36:40 GMT

Thx for your answers !

I wanna match on hour !

thx

Re: Join on datetime

martinpohl — Sat, 19 Mar 2016 15:44:55 GMT

so load both timestamps as

timestamp(floor(yourfield,1/24),'YYYY-MM-DD hh')

Re: Join on datetime

maxgro — Sat, 19 Mar 2016 16:25:06 GMT

add a field to associate the 2 tables

I added some test data to your script

Data1:

load

Time(floor(Timestamp#(Header1,'YYYY-MM-DD hh:mm:ss'), 1/24)) as JoinField

Inline [

Header1, Header2, Header3

2016-03-16 08:05:12, Action 1, Data 1

2016-03-16 09:05:12, Action 2, Data 2

2016-03-16 10:05:12, Action 3, Data 3

];

Data2:

load

Time(floor(Timestamp#(Header11,'YYYY-MM-DD hh:mm:ss'), 1/24)) as JoinField

Inline [

Header11, Header 22

2016-03-16 08:00:00, John DOE

2016-03-16 09:00:00, Alan

];

Re: Join on datetime

Sat, 19 Mar 2016 17:01:58 GMT

Thx a lot could you explain me the formula ?

Re: Join on datetime

sunny_talwar — Sat, 19 Mar 2016 22:39:34 GMT

The formula is just truncating everything after the Hours so that when you match them, you only match them upto the hour level