Join on datetime

[#1]

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

[sunny_talwar]

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

[MarcoWedel]

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

regards

Marco

[#4]

Thx for your answers !

I wanna match on hour !

thx

[martinpohl · Accepted solution]

so load both timestamps as

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

[maxgro]

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

];

[#7]

Thx a lot could you explain me the formula ?

[sunny_talwar]

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