Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
veeranj
Creator II
Creator II

To show data till date with latest status

Hi All ,

 

I have data in below format .

  

customerid eventtimestamp status

C1
JAN 1st 10 AM
A
C2
JAN 1st 11 PM
A
C3
JAN 1st 3 AM
A
C4
JAN 1ST 12 PM
A
   
C1
JAN 1st  11:30 PM
B
C2
JAN 1st  11:35 PM
B
C1JAN 3rd 10 AMC

 

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

C1JAN 1st  11:30 PMBJAN 1ST
C2JAN 1st  11:35 PMBJAN 1ST
C3JAN 1st 3 AMAJAN 1ST
C4JAN 1ST 12 PMA
JAN 1ST
    
C1JAN 1st  11:30 PM
B
JAN 2ND
C2JAN 1st  11:35 PM
B
JAN 2ND
C3JAN 1ST 3AMAJAN 2ND
C4JAN 1ST 12 PMAJAN 2ND
    
C1
JAN 3rd 10 AM
CJAN 3RD
C2JAN 1st  11:35 PMB
JAN 3RD
C3
JAN 1ST 3AM
A
JAN 3RD
C4JAN 1ST 12 PMAJAN 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

 

 

 

Labels (2)
0 Replies