Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Finding the Consecutive 3 or more zeros and Changing less than Consecutive 3 Zeros to 1

varun86vgopal
6 - Meteoroid

Hi,

 

Trust you all are well

 

I have a large dataset. That is with rows when transposed, have 26,874,045. A sample data is attached. 

When we include Append option, the same is taking a lot of time. The workflow didn't even complete after 15 Hours.

 

The data have:

1. Brands

2. Selling Points

3. Date: from 01-10-2018 to 01-12-2020

4. Values: 1 - They ordered. 0- They Didn't ordered

 

Condition to take into Consideration:

To Calculate Lost Customer Rate per Month: Count of Lost Flag in that Particular Month / Count of Active Sell Points in the Previous Month

Explanation

 

varun86vgopal_2-1626076393837.png

 

1. Group by Brand then Group  by Selling Points.

2. After 1, if 3 or more consecutive zeros come it is considered as 1 Lost flag. The month at which the pattern of consecutive 3 or more zero started is the first Lost Month and only that month is taken for Lost calculation. So in the below example: 

 

01/06/2020 is one Lost Flag and 01/02/2019 is another Lost Flag for the Same Brand (A2) in sell point (8). So in this example only 2 Lost Flag. We  are not taking 01/07/2020, 01/07/2020, for Lost Calculation but they are still inactive Sell Point.

varun86vgopal_1-1626075564217.png

 

 

3. If there are less than 3 consecutive zeros, it should be considered as sales active months.

For Example: Here the two zero months should be considered as active month for Sell Point:

varun86vgopal_0-1626075340810.png

 

4. Calculate Count of Active Sell Points in the Previous Month: If we are calculating for April, we should get the total count of One in March (Please refer the first screenshot.

 

Once this flags are achieved we can ignore the group by Sell Point and bring in Group By Date, so that we can calculate it for particular month.

 

Sample data is attached.

 

Is it possible to create a workflow without using the Append Function?

 

Highly Appreciate your help!

 

 

 

 

 

26 REPLIES 26
varun86vgopal
6 - Meteoroid

Hi @bensilv

 

I tried using the group by in both the workflow, but for some reason, it is also taking the zeros before the first one in a specific Brand for a specific sell point.

 

Thanks for your help.

atcodedog05
18 - Pollux

Hi @varun86vgopal 

 

Thank you for that clarification. I made a small change. I changed values does not exist as Null this way first 0's are not considered. Only 0's after first 1 is taken in consideration.

atcodedog05_0-1626102179053.png

 

Please check and let me know.

 

Hope this helps : )

 

bensilv
Alteryx
Alteryx

Perhaps @atcodedog05 can modify his workflow as it seems to be almost there!

 

But I can explain the last step. With the multi-row formula tool, you can set the "Values for Rows that don't Exist" to Null. By doing this, you can add an additional clause to the IF statement to say "IF the previous row value is null, then set as 1" as an example. 

bensilv_0-1626102265011.png

 

 

With the above said, that addition should have all the logic you need with these groups. 

varun86vgopal
6 - Meteoroid

Hi bensilv,

 

One more condition mentioned was only first month of the 3 or more consecutive months should be taken for the Inactive calculation. Really sorry if I confused you. So in the below example, only for 01/06/2020 - It is inactive. Rest of the zeros shouldn't be taken for Inactive calculation.

 

varun86vgopal_0-1626103371329.png

Thanks a lot.

atcodedog05
18 - Pollux

Hi @varun86vgopal 

 

Here you go only first month is considered for inactive.

 

Workflow:

atcodedog05_0-1626104014730.png

 

Hope this helps : )

varun86vgopal
6 - Meteoroid

Thanks a lot both. Let Me try this out. You guys are way too Amazing!!! 🤗

 

I will keep you both posted.

varun86vgopal
6 - Meteoroid

@atcodedog05  

 

This one is super perfect for the Lost Flag. 🤗

 

One more part is there ie the Active Flag = All One + Zero upto two consecutive (If there are 3 or more consecutive zeros, even the first two zeros shouldn't be considered.

 

Lost Brand Rate = Count of Lost Flag / Count of Active Flag of previous month.

 

Eg: if we are calculating for 1 April 2019 = Count of Lost Flag for April / Count of Active Flag For March.

 

Thanks a million for your help.

atcodedog05
18 - Pollux

Hi @varun86vgopal 

 

Here is the workflow for active flag based on your logic.

 

Workflow:

atcodedog05_0-1626108505059.png

 

Hope this helps : )

varun86vgopal
6 - Meteoroid

Hi @atcodedog05 

 

You are a superstar brother. It is working perfect. Thanks a million for the help today. Stay Safe! 🤗

 

Thank You!

atcodedog05
18 - Pollux

Happy to help : ) @varun86vgopal 

 

Thank you, same to you : )

Labels