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.

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
11 - Bolide

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
19 - Altair
19 - Altair

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