Solved

Max Date Time from Partitioned by Client

  • 1 December 2022
  • 5 replies
  • 117 views

@izzy 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

 

icon

Best answer by sunnygudVZ 9 December 2022, 01:30

View original

5 replies

Userlevel 7
Badge +1

You would need a window function for that. There are some hacky ways to achieve it, though not always working but here’s a similar topic:

And a link to product request that definitely needs more votes: https://portal.feedback.us.pendo.io/app/#/case/24417

https://portal.feedback.us.pendo.io/app/#/case/115822?

@Dawid , the expectation is to get the Max date in a given group. I dont think the above % of population would work for us. 

Userlevel 7
Badge +1

It’s not a solution. There isn’t one out of the box but the concept you’re after is the same. Have a look here: https://community.looker.com/blog-archives-1027/a-window-into-the-soul-of-your-data-29363

@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 : 
 

  1. Convert the date to a unique number, (since in our case we are using mySQL we created a dimension as follows : 
      dimension: end_time_value {
    type: number
    sql: unix_timestamp(${TABLE}.end_time) ;;
    }
  2. Add the following Dimensions to the Look
    • Client
    • End Date
    • End Date Value(Unix Timestamp)
       
  3. Create a Table Calculation “partition_row_by_client”for creating a Row Partition 
    if (
    match(${table.client},${table.client})=offset(match(${table.client},${table.client}),-1),
    1+row()-match(${table.client},${table.client}),
    1)
  4. Create a Table Calculation “max_unix_timestamp_by_client” (This is to get the Max value in the given partition)
    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)
  1. Create a Table Calculation to Lookup the Date Time (The reason why we do this step is, Step4 only brings the Unix Timestamp and probably you wouldnt want your visualization. )
    lookup(${max_unix_timestamp_by_client},${table.end_date_value},${table.date})
  2. Create a Table Calc “Hide if No From Viz” for hiding the NO’s
    if(${max_unix_timestamp_by_machine_name}>0,yes,no)
  3. Hide the following from Viz :
    • partition_row_by_client
    • max_unix_timestamp_by_client
    • Hide if No From Viz

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 

 

Userlevel 7
Badge +1

Ah yes, the hacky table calculations - I’m glad you found it and got it to work!

Reply