Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yangcelinexu
Contributor

join two file with datetime but datetime have different format and content

Hi , I have 2 file from 2 system so have the datetime with different format

as follow

Screen Shot 2018-02-28 at 10.02.51.png

is that the case I need to change the righthand side datetime format?

I also have date, time, and the_hour_of_a_day exist in the file.

if datetime not working, could I use 'Date' and 'time' join at the same time to represent the datetime?

if I can, how?

and if I have the right format

could i use both datetime and also the other floor to join the data together? (inner join will be nice)

here is the app with the data for your info

Thank you!

1 Solution

Accepted Solutions
zebhashmi
Valued Contributor

Re: join two file with datetime but datetime have different format and content

file

6 Replies
YoussefBelloum
Esteemed Contributor

Re: join two file with datetime but datetime have different format and content

Hi,

first thing to know is the format of your 2 date fields:

on TempNew.dateTime, it is clear that it's a Timestamp format (YYYY/MM/DD hh:mm:ss)

but what is the format on the left part (Datetime) ? YYYY/MM/DD ??:??

zebhashmi
Valued Contributor

Re: join two file with datetime but datetime have different format and content

Date(floor(Date#([datetime],'YYYY.MM.DD.hh.mm')),'MM/DD/YYYY') as [datetime],

zebhashmi
Valued Contributor

Re: join two file with datetime but datetime have different format and content

file

Digvijay_Singh
Honored Contributor III

Re: join two file with datetime but datetime have different format and content

If possible avoid join by timestamp fields, internally they are decimal numbers and chances are very high to have unmatched records which might result in cross join.

yangcelinexu
Contributor

Re: join two file with datetime but datetime have different format and content

Thank you so much!

I test it, it is more accurate using 'Date'+'the hour of a day'+'floor' to join

if use datatine join i have 'the hour of a day' difference from different csv.

I did not know why

yangcelinexu
Contributor

Re: join two file with datetime but datetime have different format and content

Thank you for the info. I thought maybe because of the time zoon different in the setting which I can not see