To show data till date with latest status

veeranj — Sat, 16 Nov 2024 05:15:57 GMT

Hi All ,

I have data in below format .

customerid eventtimestamp status

JAN 1st 10 AM

JAN 1st 11 PM

JAN 1st 3 AM

JAN 1ST 12 PM

JAN 1st 11:30 PM

JAN 1st 11:35 PM

JAN 3rd 10 AM

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

JAN 1st 11:30 PM

JAN 1ST

JAN 1st 11:35 PM

JAN 1ST

JAN 1st 3 AM

JAN 1ST

JAN 1ST 12 PM

JAN 1ST

JAN 1st 11:30 PM

JAN 2ND

JAN 1st 11:35 PM

JAN 2ND

JAN 1ST 3AM

JAN 2ND

JAN 1ST 12 PM

JAN 2ND

JAN 3rd 10 AM

JAN 3RD

JAN 1st 11:35 PM

JAN 3RD

JAN 1ST 3AM

JAN 3RD

JAN 1ST 12 PM

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

topic To show data till date with latest status in QlikView

To show data till date with latest status