@izzymiller or anyone can you give some suggestion, if there is a simpler way to do it?
We have clients, and dates with timestamps… Need to get Max of Date.
Sample Data
S.NO | Client | Date |
1 | AA-BBB-VM9 | 2022-11-26 15:32:42 |
2 | AA-BBB-VM9 | 2022-11-29 07:32:50 |
3 | AA-BBB-VM9 | 2022-11-28 23:32:38 |
4 | AA-BBB-VM9 | 2022-11-28 15:32:41 |
5 | AA-BBB-VM10 | 2022-11-28 07:32:37 |
6 | AA-BBB-VM10 | 2022-11-27 23:32:37 |
7 | AA-BBB-VM10 | 2022-11-27 15:32:55 |
8 | AA-BBB-VM10 | 2022-11-27 07:32:41 |
9 | AA-BBB-VM10 | 2022-11-26 23:32:40 |
10 | AA-BBB-VM10 | 2022-11-30 13:01:49 |
11 | AA-BBB-VM11 | 2022-11-26 07:32:42 |
12 | AA-BBB-VM11 | 2022-11-25 23:32:37 |
13 | AA-BBB-VM11 | 2022-11-25 15:32:39 |
14 | AA-BBB-VM11 | 2022-11-25 07:32:39 |
15 | AA-BBB-VM11 | 2022-12-01 07:04:26 |
Solved! Go to Solution.
@Dawid , sorry for not being very clear on the requirement. (i read my initial question it is condescending and the solution you have is perfect.)
What we were trying to do was on a Looker UI and specifically using Table Calculations, we didnt want to create a Derived table and additional explore for a reason.
After several digging i was able to find a solution that resolved my issue.
Core Problem : Finding a Max Date in a given partitioned window, the problem is the date time format and finding an max value in it.
Preparation :
dimension: end_time_value {
type: number
sql: unix_timestamp(${TABLE}.end_time) ;;
}
if (
match(${table.client},${table.client})=offset(match(${table.client},${table.client}),-1),
1+row()-match(${table.client},${table.client}),
1)
if(
NOT(${table.client} = offset(${table.client},1)),
max(offset_list(${table.end_date_value},-(${partition_row_by_client}-1),${partition_row_by_client})),null)
lookup(${max_unix_timestamp_by_client},${table.end_date_value},${table.date})
if(${max_unix_timestamp_by_machine_name}>0,yes,no)
You can Enhance this based on your need.
The whole purpose of converting the date time in to UnixTimestamp is to allow “offset_list()” to work.
Else, offset_list() cannot identify date as an INT and would fail the implementation.
P.S : The above solution ?? is only helpful if you prefer to do a Table Calc as opposed to that of a Derived Table Partition.
If you are using a Derived Table please follow the solution provided by @Dawid