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

Help! dynamic filter in-DB

ascovell
5 - 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!

2 REPLIES 2
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. 

ascovell
5 - 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