Join using Date and Time
We are trying to compare transaction logs and are trying to make a join using a date field and a time field. We think the issue we are running into is one of the reports is showing the time as HH:MM:00, while the other is showing it with the actual seconds (HH:MM:SS). What is the best way to drop the seconds on both so that we can join. We tried everything we can think of, but our brain power is running out so there probably are a number of easy, common sense ways to do it that we aren't thinking of.
Thanks!
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------
Answers
-
Hi Jon,
Did you try selecting the column and use the FORMAT Option from the drop-down menu and then selecting the CUSTOM Option to allow you to define how you would like to define the date/time field. From CUSTOM- select the fields you want. Note: you may want to double check the field is on the date field.
------------------------------
Rebecca Cronin
Customer Success Manager
Bedford MA
(978) 275-8203
------------------------------
-------------------------------------------
Original Message:
Sent: 09-01-2020 04:12 PM
From: Jon Dix
Subject: Join using Date and Time
Hey,
We are trying to compare transaction logs and are trying to make a join using a date field and a time field. We think the issue we are running into is one of the reports is showing the time as HH:MM:00, while the other is showing it with the actual seconds (HH:MM:SS). What is the best way to drop the seconds on both so that we can join. We tried everything we can think of, but our brain power is running out so there probably are a number of easy, common sense ways to do it that we aren't thinking of.
Thanks!
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------
"0 -
Hi Jon,
I think you are correct in determining that it is the seconds that are stopping your rows from joining. The format is primarily a visual setting, so even if you choose a format that excludes some of the components, it is still in the underlying data and will prevent the join.
For example, with the two text files below, only Data Line 2 and B will 'INNER' join; regardless of the formatting options.
** Edit - the lower screen shot was taken when date & time were in one field. It should have looked like this . . .
If you don't mind losing the seconds component from the time, then the following should resolve it.- Ensure that the time is formatted to include exclude seconds. It should look identical in the table, but as you have seen, will not join at this stage. For me, the built in 'Time' format works, but this will depend on your Windows Regional Settings, so you may need to define a custom format.
- Convert the column to Character by clicking on the clock symbol in the column heading and changing it to Ab
- Your join should now match as expected (excluding seconds)
You can change the columns back to Date/Time in the joined table if you need to export a true date/time field.
If you need to retain seconds or can't change the column to character for other reasons, you will need to duplicate the column before making any changes, then edit one 'set' to perform the join.
Alternatively, you could create a new genuine date/time calculated column containing the date/time without the seconds component by subtracting the number of seconds from the original value.
Regards,Steve.
------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 09-01-2020 04:12 PM
From: Jon Dix
Subject: Join using Date and Time
Hey,
We are trying to compare transaction logs and are trying to make a join using a date field and a time field. We think the issue we are running into is one of the reports is showing the time as HH:MM:00, while the other is showing it with the actual seconds (HH:MM:SS). What is the best way to drop the seconds on both so that we can join. We tried everything we can think of, but our brain power is running out so there probably are a number of easy, common sense ways to do it that we aren't thinking of.
Thanks!
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------"0 -
Thank you Steve! We will be trying this method this afternoon. We had tried something similar but it was toward the end of a particularly rough day and so we probably missed a step in there.Steve Caiels_21881 said:Hi Jon,
I think you are correct in determining that it is the seconds that are stopping your rows from joining. The format is primarily a visual setting, so even if you choose a format that excludes some of the components, it is still in the underlying data and will prevent the join.
For example, with the two text files below, only Data Line 2 and B will 'INNER' join; regardless of the formatting options.
** Edit - the lower screen shot was taken when date & time were in one field. It should have looked like this . . .
If you don't mind losing the seconds component from the time, then the following should resolve it.- Ensure that the time is formatted to include exclude seconds. It should look identical in the table, but as you have seen, will not join at this stage. For me, the built in 'Time' format works, but this will depend on your Windows Regional Settings, so you may need to define a custom format.
- Convert the column to Character by clicking on the clock symbol in the column heading and changing it to Ab
- Your join should now match as expected (excluding seconds)
You can change the columns back to Date/Time in the joined table if you need to export a true date/time field.
If you need to retain seconds or can't change the column to character for other reasons, you will need to duplicate the column before making any changes, then edit one 'set' to perform the join.
Alternatively, you could create a new genuine date/time calculated column containing the date/time without the seconds component by subtracting the number of seconds from the original value.
Regards,Steve.
------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
-------------------------------------------
Original Message:
Sent: 09-01-2020 04:12 PM
From: Jon Dix
Subject: Join using Date and Time
Hey,
We are trying to compare transaction logs and are trying to make a join using a date field and a time field. We think the issue we are running into is one of the reports is showing the time as HH:MM:00, while the other is showing it with the actual seconds (HH:MM:SS). What is the best way to drop the seconds on both so that we can join. We tried everything we can think of, but our brain power is running out so there probably are a number of easy, common sense ways to do it that we aren't thinking of.
Thanks!
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------"
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------
-------------------------------------------
Original Message:
Sent: 09-02-2020 05:35 AM
From: Steve Caiels
Subject: Join using Date and TimeHi Jon,
I think you are correct in determining that it is the seconds that are stopping your rows from joining. The format is primarily a visual setting, so even if you choose a format that excludes some of the components, it is still in the underlying data and will prevent the join.
For example, with the two text files below, only Data Line 2 and B will 'INNER' join; regardless of the formatting options.
** Edit - the lower screen shot was taken when date & time were in one field. It should have looked like this . . .
If you don't mind losing the seconds component from the time, then the following should resolve it.- Ensure that the time is formatted to include exclude seconds. It should look identical in the table, but as you have seen, will not join at this stage. For me, the built in 'Time' format works, but this will depend on your Windows Regional Settings, so you may need to define a custom format.
- Convert the column to Character by clicking on the clock symbol in the column heading and changing it to Ab
- Your join should now match as expected (excluding seconds)
You can change the columns back to Date/Time in the joined table if you need to export a true date/time field.
If you need to retain seconds or can't change the column to character for other reasons, you will need to duplicate the column before making any changes, then edit one 'set' to perform the join.
Alternatively, you could create a new genuine date/time calculated column containing the date/time without the seconds component by subtracting the number of seconds from the original value.
Regards,Steve.
------------------------------
Steve Caiels
Professional Services
Altair
------------------------------
Original Message:
Sent: 09-01-2020 04:12 PM
From: Jon Dix
Subject: Join using Date and Time
Hey,
We are trying to compare transaction logs and are trying to make a join using a date field and a time field. We think the issue we are running into is one of the reports is showing the time as HH:MM:00, while the other is showing it with the actual seconds (HH:MM:SS). What is the best way to drop the seconds on both so that we can join. We tried everything we can think of, but our brain power is running out so there probably are a number of easy, common sense ways to do it that we aren't thinking of.
Thanks!
------------------------------
Jon Dix
Research Analyst
Founders Federal Credit Union
Lancaster SC
(803) 289-5277
------------------------------"0
Altair Employee