In-Database Incrementation on Condition Using Fomula

Question

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?

danilang · Answer

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

dataguyW · Answer

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().

sartol · Answer

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?