CODEWITHSUNDEEP
sqlspotfire
Brett Allen
Brett Allen

Reputation: 1

Doubleor triple timestamp issue

I am using SQL assistant and my data brings in snapshots from a huge database in the form of timestamps. Occasionally the snapshots bring in multiples per hour. The data is correct, multiple snapshots do happen from time to time within an hour, not always but it does happen.

I am bringing this into Spotfire and viewing by an hour and when more than one snapshot happens in the hour, the data shows as doubled.

I only want to display one per hour preferably the last(max) timestamp for the hour. Example; for the 7 am hour the data has a snapshot for 7:10 am and one for 7:55 am.

These are correct but I only want to display the last(max) timestamp, 7:55 am in this case. I can't figure the issue out in Spotfire so I am leaning towards a fix in SQL. How can I display only 1 for each hour?

Upvotes: 0

Views: 78

Answers (1)

Chelsea
Chelsea

Reputation: 511

You'd do this similarly to how you'd probably do it in SQL -- using a ranking/rownumber function.

The basic way Rank in Spotfire works is Rank(Order columns, order direction, partitioned columns, tie method)

You need to partition by the combination of Date and Hour, and then sort descending by your timestamp column.

So the code to identify the rows that you want to isolate should be something along the lines of:

Rank([TimestampColumn], "desc", Date([TimestampColumn]), Hour([TimestampColumn]), "ties.method=first")

What you do with it from here is going to depend on how you plan to use the data - for example, you can Limit Data Using Expression and set the code above = 1 which will limit your table accordingly (helpful if you don't want your users to accidentally forget to filter), or you can create a calculated column which turns it into a flag of some form like here:

    If(Rank([TimestampColumn], "desc", Date([TimestampColumn]), Hour([TimestampColumn]), "ties.method=first") = 1, "Latest", "Duplicate")

Which allows your users to filter by this property. This way, they have the option to look at the extra rows.

Ultimately, though, if you want to only ever see these rows, and have no use for the earlier records, I'd probably do it in SQL, if you have that ability. This reduces the number of rows you have to load into your analytic.

Upvotes: 1

Related Questions