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.

In-Database Incrementation on Condition Using Fomula

sartol
7 - Meteor

Hello!

I need to count how many times something occurs with less than a minute between each occurrence.

I decided to create a new column that starts at 0 and adds one every time the idle duration exceeds 60sec and then group by cluster using the summary tool and count the number of members to that cluster (identified by the unique number).

 

However, the In-Database formula took keeps giving me an error.

if "idle_duration" >60 then +1

 

How can I fix this?

3 REPLIES 3
sartol
7 - Meteor

As an alternative, I used the data stream out tool to take it out of In-Database tools and used the multi-row formula tool.

The issue now is that the run time is excessive (still hasn't finished inching out of the database).

Is there anything better?

john_watkins
8 - Asteroid

I don't know exactly which database type you are connecting to, but most have a LAG/LEAD analytic function (aka Alteryx Multi-Row) to find the time differences and a ROW_NUMBER() to get a unique counter (aka Alteryx RecordId).  It is a shame to have it IN-DB and bring it back until you've done the bulk of your work.  

 

You may need multiple formula tools as I don't believe you can created a field in one and also reference the same field again later in the tool.  Each IN_DB tool is building SQL behind the scenes so you may need one to determine if the times are <> 60 seconds and then another formula to add the RowNumber().

danilang
18 - Pollux
18 - Pollux

Hi @sartol 

 

If you Filter In-DB where "idle_duration" >60 and follow this with a Summarize In-DB that groups by cluster and counts the records you can get the results very quickly

 

Dan

Labels