Alteryx designer Discussions

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

Help! dynamic filter in-DB

Highlighted
Atom

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!

Highlighted
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. 

Highlighted
Atom

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

Labels