Hi,
I tried adding this in my query but i am not getting correct result.
e.g consider i am running this for last 7 days ( from 4th Feb to 11th Feb), then for all the IDs I am getting missing dates as all the dates from 5th Feb-10th Feb.
This is how it is working:
1. In the step where you are adding info_min_time and info_max_time, it is adding 4th Feb (min time) and 11th Feb (max time) for every ID.
2. In the step where you are finding ingestion_epoch, it is getting calculated only for the above dates (4th Feb and 11th Feb). For the rest of the dates, ingestion_epoch is coming blank.
3. In the sorting step, for every ID its sorting like this- 4th Feb, 11th Feb, other dates present for that ID (as ingestion_epoch is blank for them).
4. Then in the streamstats, i am getting previous result values like this->
ID ingestion_date Prev
1 4th Feb
1 11th Feb 4th Feb
1 5th Feb 11th Feb
etc.
5. So, when mvrange function is used, its giving all the dates from (5th Feb-11th Feb) for the record with ID=1 and prev=4th Feb from the above table.
6. with where isnotnull(Missing), final result is only picking row from above and showing it as result.
Please let me know where it is going wrong.
Thanks again!
... View more