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

Sample every nth record in DB based on column

shivraj_r
7 - Meteor

Hey Everyone,

 

I'm trying to sample every nth record from a column in a DB with timestamps in it. For example if I wanted to specify that I wanted to sample every 10 seconds starting with the first row - what's the most efficient way to do this? I tried the sample in DB tool but that only allows for percentage/ number of records.

 

My data set looks like this -

 

msg iddatatimestamp_sinput_timestamp_scvdt35_bus_r
1290x00000000000000004/24/2018 16:2015245868072
1290x00000000000000004/24/2018 16:2015245868082
1290x00000000000000004/24/2018 16:2015245868092
1290x00000000000000004/24/2018 16:2015245868062
1290x00000000000000004/24/2018 16:2015245868102
1290x00000000000000004/24/2018 16:2015245868112
1290x00000000000000008/14/2018 3:1315342163882
1290x00000000000000008/14/2018 3:1315342163892
2 REPLIES 2
MarqueeCrew
20 - Arcturus
20 - Arcturus

@shivraj_r,

 

Using either logic looking for specific seconds (e.g. in (10,20,30)) or using a MOD function (b = mod( a , m ) returns the remainder after division of a by m , where a is the dividend and m is the divisor) I would filter the data to get only the records that hit those marks.  Depending upon your database, the SQL will vary as not all SQL is the same.

 

Random is easier if you can assign a random number (SQL varies) and pull out the random data that matches your % or value.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
shivraj_r
7 - Meteor

@MarqueeCrew

 

Thanks for the suggestion! I'll try it out on the sql side using the mod function!

Labels