Selecting data from one file only when multiple conditions met

[Rumanais]

Hello,

I am pretty new to Alteryx. I have been trying to solve this problem which seems simple, but cannot seem to find an appropriate solution. 

What I am trying to accomplish:

I have a file with fields: Customer_ID, Login_Date, Login_Tiemstamp, login_method, Login_outcome

There are multiple login in same/multiple days for each customer. The login outcome could be success or failure.

desired outcome:

from this file, I would like to have output of three different datasets:

1. The customer IDs with only success with no failure

2. The customer IDs with only failure with no success

3. The customer IDs that have both success and failure 

For exported files/datasets 1 & 2,  I do not need to do anything further.

For the file/dateset # 3, I would like to see: If there are >=2 successive "failure", there is a "success" greater >2 mins later. The result should contain all of the fields within the file including all of the corresponding customer IDs.  

Note: sample data file attached

[Qiu]

@Rumanais 
I hope I understand you correctly.

And if yes, it is a quite interesting one.

😁

[Rumanais]

Hello Qiu,

Thank you for working on this problem.  I will have access to Designer on Monday to look at the flow. I will let you know if this would work!

Thank you so much.

-Rumana

[Mchappell67]

Hi - 

This is what I came up with:

Note that if you are ONLY concerned with Dataset 3 (fails>2, but success >2 minutes, you only need container 1 and container 4.  If you want to see all the datasets described, then you need containers 1+2+3.  Containers 3 and 4 are identical, apart from the input source.  Also, note that I added a "Dataset #4", which is for customers where they have fails>2 and either a successful login <2 minutes or NO successful login.

Also, I don't believe that your sample data included any situations that satisfied Dataset #3, so I modified the timestamp for the successful login on 13-October so that it was >2 minutes since the last failed attempt.

Mark

[Rumanais]

Hi Mchapell67,

Thank you for the solution! However, since the input data for your flow was unavailable for me, I was not able to test it.  Can you please change your input source to a "Text Input" with my sample dateset? 

Much appreciated

-Rumana

[Rumanais]

Hi Qiu,

I am reviewing the flow with the actual data.  I will post the updated workflow if I make any changes. 

Thanks again!

-Rumana

[Mchappell67]

Hi -

All I did was modify the timestamp on one record, as noted.

Anyhow, attached is the file that I used.

Mark

[Rumanais]

Hi Qiu,

I have been able to use the workflow you provided and was able to solve my problem with some minor tweaks to the workflow. For my actual problem I had over 500k data and my step 3 requirement was 5 consecutive FAILED login before "SUCCESS" and the login "SUCCESS" has to be after 2 minutes.

Thank you So much for your help!

Here is the updated flow.

[Rumanais]

Hi Mchappell67,

I just got a chance to review the workflow.  I think with some minor tweak, this workflow should be used for the problem I was trying to solve as well.  Thank you for taking the time to create the workflow. Much appreciated!!

-Rumanais