Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Help! dynamic filter in-DB


Hi all,


I think there must be a better way to achieve what we are doing, but struggling to implement it right now:


Screen Shot 2017-12-21 at 00.20.03.png



This is the start of our process - what it is basically doing is isolating any new records in two SQL tables before they are sent through other processes.  But i think it is extremely in-effecient.  It imports the entirety of both tables and then de-dupes against already processed records stored in an xydb file.


What i need i think is to use a dynamic in-db filter to only extract the data needed.


Just been getting my head round batch macros and have built this:


Screen Shot 2017-12-21 at 00.24.55.pngScreen Shot 2017-12-21 at 00.25.25.png


Which i think is doing what i need (working out what data it needs and then updating the filter to select that data).  However this is not in-db which means i still need to extract the whole table, which is what i am trying to avoid.


I'm a bit baffled as to what the in-db macro in/out is and does - i think if i could do the above, but for in-db, that would probably solve my issue.


Any suggestions would be very gratefully received!

Inactive User
Not applicable

From what I see you are taking 2 DB tables and joining them to records from a YXDB, and taking the records that do not match in the YXDB file (ie the new records). If you store this YXDB as a DB table you can take the records and do a left outer join to each of these tables. Then add a filter tool after that and set the filter to require one of the fields from the YXDB to be not-null. Then stream out.


Alternatively you could add a field to your DB tables such as last modified date or processed flag and do filters based on that. 


Brilliant - thanks very much.


I had looked at in-db join before but had never noticed that you could change the join type. 


a processed flag would be even more efficient i think as per your suggestion, but i need to wait for a colleagues assistance with that.


Here is the new solution:


(processed.xydb is the same as the 'events' DB). 


18 seconds vs over 3 mins previously :)  :)  :)

Screen Shot 2017-12-21 at 13.24.22.png