Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the data as shown above , i want to find the max timestamp related to an id and corresponding activity and evt_type.
so there would be just one row for a unique id and the related fields.
attached is the data sheet
Please help me the feedback . thanks
count(distinct if(HDR_TIMESTAMP = aggr(NODISTINCT max(HDR_TIMESTAMP), HDR_CORR_ID), HDR_CORR_ID))
it gives me the correct count when i type this.
not when i take hdr_event_type, why would that be ?
also when i click on the value it changes to a value which is not a correct number .
with your current statement you are calculating the max(timestamp) per ID, which is not what you wanted.
You wanted the max(timestamp) per ID & Event & Activity.
So you need to aggregate over this three dimensions:
count(distinct if(TIMESTAMP = aggr(NODISTINCT max(TIMESTAMP), ID, [Activity Name], EVT_TYPE), ID))
count(distinct if(TIMESTAMP = aggr(NODISTINCT max(TIMESTAMP), ID, [Activity Name], EVT_TYPE), ID))
see th table in left should guve me 34 total, but if you see at distinct events , they dont add up to 34 but much more
table on the left shows me a total of 34 but yif you add up the individual counts its much more , it should add upto 34