Help with transposing user logs

[Senor_Dai]

Hi, I have a table of user logs which contain events such as:

• User Sign Up
• User Request Password Set
• Success Password Change
• Login 
• Logout

As Identifiers we have Event DateTime and User ID

From the screenshot below and attached data is it possible to flag where a user has received a SREQ and no subsequent SCH is recored within 7 days (we reload these logs daily) eg, for user 97000222870 if there hasnt been a subsequent SCH after  days of reloads  we need to set a flag 'field' to 'UserFollowUp'

As we can see Users 79873540045 and 20798279344 have successfully set their password (SCH) after the preceding SREQ event.  

If it helps SU, SREQ and SCH have to happen in this order before Login In (LI) can happen.

Any help or tips hugely appreciated.

Dai

[Saravanan_Desingh]

Try this,

tab1:
LOAD DateTime, 
     UserID, 
     EventCode
FROM
[C:\Users\Sarav\Downloads\user_logs_sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

tab2:
LOAD UserID, Concat(EventCode,'',DateTime) As Str, If(Concat(EventCode,'',DateTime) Like '*SU*SREQ*SCH*','Y','N') As UserFollowUp
Resident tab1
Group By UserID;

[Senor_Dai]

Hi @Saravanan_Desingh 

Thanks for the reply.  This is great but I'm looking to check that if there hasn't been any subsequent 'SCH' event after 7 days of a 'SREQ' event then to 'follow up' .  As we will be reloading this dataset daily - can we check using the reload date?