Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

varun86vgopal
7 - Meteor

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
7 - Meteor

Hi @atcodedog05,

 

Really sorry to bother you.

 

I was doing the other calculation part in excel i.e.  Count of Lost Flag in that Particular Month / Count of Active Sell Points in the Previous Month.

 

Can we incorporate the same in our current Alteryx workflow? Attached is the workflow.

 

Example: 1. For Brand A11 and Date: 2019-02-01, Lost Rate is 1 (Lost of Current Month)/5 (Active of Previous Month) = 0.2

2. For Brand A11 and Date: 2019-05-01, Lost Rate = 1/4 = 0.25

3. For Brand A11 and Date: 2019-09-01, Lost Rate = 1/6 = 0.166

varun86vgopal_3-1626165563469.png

 

 

Thanks a million for all your help

 

 

 

 

atcodedog05
22 - Nova
22 - Nova

Hi @varun86vgopal 

 

Here you go.

 

atcodedog05_0-1626166887644.png

 

Hope this helps : )

 

varun86vgopal
7 - Meteor

🤗Thanks a lot @atcodedog05!!! 

 

It did work super perfect. 

 

Thanks a million. 🤗

atcodedog05
22 - Nova
22 - Nova

Happy to help : ) @varun86vgopal 

varun86vgopal
7 - Meteor

@atcodedog05 Hi. Trust you are well.

 

Is it possible to replace Zero to One. For Example in data attached, I want to replace all the 0 (Number) to 1(Number) in Active and Keep the rest as it is. Only zeros should be changed to one

 

Thanks for all your help.

atcodedog05
22 - Nova
22 - Nova

Hi @varun86vgopal 

 

Here is how you can do it. Using below formula.

 

IF [Active ]=0 THEN 1 ELSE [Active ] ENDIF

 

Workflow:

atcodedog05_0-1626967895175.png

 

Hope this helps : )

 

varun86vgopal
7 - Meteor

@atcodedog05  

 

As always this one works perfect. Thanks a million!!! 🤗

Labels