Hi!
From a datasource I have two different timestamps that I would like to merge into one. I have [UsStartTidpunkt] and [RemissInlästTidpunkt], is there any way that you can get these two in to one time (hour) by using a calculation with extract(hour; or is there a another way to do this?

I´m planning to use the merged time (hour) in a dashboard (X-axis)


------------------------------
Leif Glaes
------------------------------
#CognosAnalyticswithWatson

I'm confused how you want to merge the timestamps.

Are you looking to add the hour from Timestamp 2 to Timestamp 1?
_add_hours([Timestamp 1] ,_hour([Timestamp 2]))

Are you looking to use the date from Timestamp 1 and the time from Timestamp 2?
_start_of_day([Timestamp 1]) + ([Timestamp 2] - _start_of_day([Timestamp 2]))


------------------------------
Paul Mendelson
------------------------------

Hi!
Trying to clarify my request. I have two dashboards from the same source.
Dashboard nr 1 is showing the numbers of incoming X-ray remiss per month, distributed per hour (exampel: during the month of august there were 112 incoming X-ray remiss at two o´clock and 155 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [RemissInlästTidpunkt].
Dashboard nr 2 is showing the numbers of conducted X-ray examinations per month, distributed per hour (00-23), (exampel: during the month of august there were 58 conducted X-ray examinations at two o´clock and 66 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [UsStartTidpunkt].

I now want to combine this two dashboards into one (visualisation using the tabel- and line diagram) and I cant figure out how the combine the two differnt timestamps for the X-axis - showing the hours from 00 to 23.





------------------------------
Leif Glaes
------------------------------

Original Message:
Sent: Wed September 15, 2021 07:59 AM
From: Paul Mendelson
Subject: Merge two timestamps

I'm confused how you want to merge the timestamps.

Are you looking to add the hour from Timestamp 2 to Timestamp 1?
_add_hours([Timestamp 1] ,_hour([Timestamp 2]))

Are you looking to use the date from Timestamp 1 and the time from Timestamp 2?
_start_of_day([Timestamp 1]) + ([Timestamp 2] - _start_of_day([Timestamp 2]))


------------------------------
Paul Mendelson
------------------------------

Hi Leif, You can create a Data Module and select the table as a source two times by using an alias/view. You each select only one time axis/column by using a different source colum for it. Afterwards you create a new table by using Union for the two source tables (remember to switch in it's default property to "keep duplicates"). Depending on your data model you might need to add calculations to make two key figures out of one (one for the line and one for the bars).

------------------------------
Philipp Hornung
------------------------------

Original Message:
Sent: Wed September 15, 2021 06:07 PM
From: Leif Glaes
Subject: Merge two timestamps

Hi!
Trying to clarify my request. I have two dashboards from the same source.
Dashboard nr 1 is showing the numbers of incoming X-ray remiss per month, distributed per hour (exampel: during the month of august there were 112 incoming X-ray remiss at two o´clock and 155 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [RemissInlästTidpunkt].
Dashboard nr 2 is showing the numbers of conducted X-ray examinations per month, distributed per hour (00-23), (exampel: during the month of august there were 58 conducted X-ray examinations at two o´clock and 66 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [UsStartTidpunkt].

I now want to combine this two dashboards into one (visualisation using the tabel- and line diagram) and I cant figure out how the combine the two differnt timestamps for the X-axis - showing the hours from 00 to 23.

------------------------------
Leif Glaes
------------------------------


Original Message:
Sent: Wed September 15, 2021 07:59 AM
From: Paul Mendelson
Subject: Merge two timestamps

I'm confused how you want to merge the timestamps.

Are you looking to add the hour from Timestamp 2 to Timestamp 1?
_add_hours([Timestamp 1] ,_hour([Timestamp 2]))

Are you looking to use the date from Timestamp 1 and the time from Timestamp 2?
_start_of_day([Timestamp 1]) + ([Timestamp 2] - _start_of_day([Timestamp 2]))


------------------------------
Paul Mendelson

Thank´s Philipp Hornung, I will try out this solution.

------------------------------
Leif Glaes
------------------------------

Original Message:
Sent: Thu September 16, 2021 03:26 AM
From: Philipp Hornung
Subject: Merge two timestamps

Hi Leif, You can create a Data Module and select the table as a source two times by using an alias/view. You each select only one time axis/column by using a different source colum for it. Afterwards you create a new table by using Union for the two source tables (remember to switch in it's default property to "keep duplicates"). Depending on your data model you might need to add calculations to make two key figures out of one (one for the line and one for the bars).

------------------------------
Philipp Hornung
------------------------------


Original Message:
Sent: Wed September 15, 2021 06:07 PM
From: Leif Glaes
Subject: Merge two timestamps

Hi!
Trying to clarify my request. I have two dashboards from the same source.
Dashboard nr 1 is showing the numbers of incoming X-ray remiss per month, distributed per hour (exampel: during the month of august there were 112 incoming X-ray remiss at two o´clock and 155 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [RemissInlästTidpunkt].
Dashboard nr 2 is showing the numbers of conducted X-ray examinations per month, distributed per hour (00-23), (exampel: during the month of august there were 58 conducted X-ray examinations at two o´clock and 66 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [UsStartTidpunkt].

I now want to combine this two dashboards into one (visualisation using the tabel- and line diagram) and I cant figure out how the combine the two differnt timestamps for the X-axis - showing the hours from 00 to 23.

------------------------------
Leif Glaes


Original Message:
Sent: Wed September 15, 2021 07:59 AM
From: Paul Mendelson
Subject: Merge two timestamps

I'm confused how you want to merge the timestamps.

Are you looking to add the hour from Timestamp 2 to Timestamp 1?
_add_hours([Timestamp 1] ,_hour([Timestamp 2]))

Are you looking to use the date from Timestamp 1 and the time from Timestamp 2?
_start_of_day([Timestamp 1]) + ([Timestamp 2] - _start_of_day([Timestamp 2]))


------------------------------
Paul Mendelson

This is a pretty generic approach, and might not work for your use case, but in general, were I getting multiple timestamps in a query where i only needed one of them, I'd likely use a MIN or MAX function on the paired timestamps.

Hope that helps.

------------------------------
Michael Walter
Business Analyst
TX HHSC
Austin
------------------------------

Original Message:
Sent: Thu September 16, 2021 06:12 AM
From: Leif Glaes
Subject: Merge two timestamps

Thank´s Philipp Hornung, I will try out this solution.

------------------------------
Leif Glaes
------------------------------


Original Message:
Sent: Thu September 16, 2021 03:26 AM
From: Philipp Hornung
Subject: Merge two timestamps

Hi Leif, You can create a Data Module and select the table as a source two times by using an alias/view. You each select only one time axis/column by using a different source colum for it. Afterwards you create a new table by using Union for the two source tables (remember to switch in it's default property to "keep duplicates"). Depending on your data model you might need to add calculations to make two key figures out of one (one for the line and one for the bars).

------------------------------
Philipp Hornung


Original Message:
Sent: Wed September 15, 2021 06:07 PM
From: Leif Glaes
Subject: Merge two timestamps

Hi!
Trying to clarify my request. I have two dashboards from the same source.
Dashboard nr 1 is showing the numbers of incoming X-ray remiss per month, distributed per hour (exampel: during the month of august there were 112 incoming X-ray remiss at two o´clock and 155 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [RemissInlästTidpunkt].
Dashboard nr 2 is showing the numbers of conducted X-ray examinations per month, distributed per hour (00-23), (exampel: during the month of august there were 58 conducted X-ray examinations at two o´clock and 66 at tree o´clock). In this dashboard I´m using a specific timestamp for the X-axis called [UsStartTidpunkt].

I now want to combine this two dashboards into one (visualisation using the tabel- and line diagram) and I cant figure out how the combine the two differnt timestamps for the X-axis - showing the hours from 00 to 23.

------------------------------
Leif Glaes


Original Message:
Sent: Wed September 15, 2021 07:59 AM
From: Paul Mendelson
Subject: Merge two timestamps

I'm confused how you want to merge the timestamps.

Are you looking to add the hour from Timestamp 2 to Timestamp 1?
_add_hours([Timestamp 1] ,_hour([Timestamp 2]))

Are you looking to use the date from Timestamp 1 and the time from Timestamp 2?
_start_of_day([Timestamp 1]) + ([Timestamp 2] - _start_of_day([Timestamp 2]))


------------------------------
Paul Mendelson

This is probably more elaborate than you're looking for, since it's a Data Warehouse solution.  But I assign each incoming timestamp a "TimeKey".  In SQL, you use functions to extract the Hour from the full time. and assign it a key (in the case below, it is actually down to the 15 minute period.  The fields below are:
TimeKey,EuropeanTime,AmerTimeString,EuropeanHour, AmericanHour,Minutes, and MinuteOfDay,AMPM, and TimeRange. 



------------------------------
Marcus Hogins
------------------------------

I have two different dfs each with a timestamp. One is collecting data every minute, the other just in case of a change, so sometimes every second, sometimes a couple of hours in between two rows. How can I append the columns of df2 to df1 so the timestamp would be the same? df2 would show the same value in each row (so each minute) until something changes (so fill the NaN's). It's okay to lose some information through that, it's more important to put the dfs together. I'm still a beginner so help would be appreciated. Thanks in advance!

------------------------------
Muhammad Majid
------------------------------

Muhammad,
I've not been doing this very long in Cognos either, more in MS SQL.  But you can go into your background query of the report and add a new calculated field, double-click on it to define the "Expression Definition" sort of like this:

case when [yourordersource].[df2] is null then [yourordersource].[df1] else [yourdatasource].[df2] end

Marcus

------------------------------
Marcus Hogins
------------------------------

Original Message:
Sent: Mon September 20, 2021 12:22 PM
From: Muhammad Majid
Subject: Merge two timestamps

I have two different dfs each with a timestamp. One is collecting data every minute, the other just in case of a change, so sometimes every second, sometimes a couple of hours in between two rows. How can I append the columns of df2 to df1 so the timestamp would be the same? df2 would show the same value in each row (so each minute) until something changes (so fill the NaN's). It's okay to lose some information through that, it's more important to put the dfs together. I'm still a beginner so help would be appreciated. Thanks in advance!

------------------------------
Muhammad Majid
------------------------------