Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All ,
I have data in below format .
customerid eventtimestamp status
C1 |
| A | |
C2 |
| A | |
C3 |
| A | |
C4 |
| A | |
C1 |
| B | |
C2 |
| B | |
C1 | JAN 3rd 10 AM | C |
I have to show the count of customers who are in latest status as on that date .
Output should be like :
CUSTOMERID
EVENTTIMESTAMP LATESTSTATUS ASONDATE
C1 | JAN 1st 11:30 PM | B | JAN 1ST | ||
C2 | JAN 1st 11:35 PM | B | JAN 1ST | ||
C3 | JAN 1st 3 AM | A | JAN 1ST | ||
C4 | JAN 1ST 12 PM | A |
| ||
C1 | JAN 1st 11:30 PM |
| JAN 2ND | ||
C2 | JAN 1st 11:35 PM |
| JAN 2ND | ||
C3 | JAN 1ST 3AM | A | JAN 2ND | ||
C4 | JAN 1ST 12 PM | A | JAN 2ND | ||
C1 |
| C | JAN 3RD | ||
C2 | JAN 1st 11:35 PM | B |
| ||
C3 |
|
| JAN 3RD | ||
C4 | JAN 1ST 12 PM | A | JAN 3RD |
The same status has to be carried forward to next eventdate-1 for given customerid .
If particular customerid has TWO eventtimestamps and we have to take latest STATUS FOR THAT DAY FOR THAT CUSTOMER .
I would like to count the no.of customers who are having status in A,B,C on that date(asondate) .
Once the asondate got passed ,i would like to count the values and store it in Qvd and only for the latest generation date,i want to count the metrics based on status and append that to qvd.
If i keep this much base data at backend and in frontend if i write ,count (customerid),dashboard size will get increased on daily basis .
So i want to keep only aggregated values .
Output shuld be like :
Date ,STATUS,COUNT
JAN 1ST B 2
JAN 1ST A 2
JAN 2ND B 2
JAN 2ND A 2
JAN 3RD A 2
JAN 3RD B 1
JAN 3RD C 1
This aggregated data should be keep on getting generated till today-1 date .
How to achive this .
I wrote a FOR loop to generate the missing records and with the help of FIRSTSORTEDVALUE function .
Thanks,
Veera/Anjee