Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a table of user logs which contain events such as:
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
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;
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?