I need to keep the first record of each member on the Server (IN-DB)

[JavaGirl]

So using the standard sample tool i can retain the first record for each member. I was using the standard sample tool as shown below.   However i would like to do this on the server using the in-DB.  The sample tool of the in-db doesn't seem be structured this way. Is it possible to do this on the server?  I need to continue blending some data and then update a DB2 table.   The only other issue i'm having is sorting on the Server (outside of the sql code).  

Does anyone have experience or a tool trick on how to complete these items on the Server? 🤞

[JagdeeshN]

@JavaGirl 

Have you tried using a SQL query within the In-DB tools to get this done.

select *, row_number() over (order by [group_by_field]  desc) as rank from [table_name]

where rank <= 1;

[JavaGirl]

@JagdeeshN   I used SQL inside of the Connect In-DB.   which is great - but i need to be able to do this functionality after some data blending.  Below is a screen shot of wht i'm attempting to do.  I may not be thinking like "Alteryx" right now.  ;)

[JagdeeshN]

@JavaGirl  What is the volume of data being processed within the workflow?

I ask to understand the reasoning behind using In-DB?

[JavaGirl]

@JagdeeshN   This is an ETL process.  We are pulling data from ftp site or other data sources and then we need to update the DB2 table data internally.  Volume in this case is updating about 200,000 records. I would like to keep the speed and ability to keep it on server.  The standard output tool is taking approximately 10 -12 minutes.   Also, this is a use case example.  The volume can be 2-3 million records at a time.  Therefore this process will give us an example as to whether or not the tool can complete the ETL as needed in our company. 

I tried using the stream in and out tools but they are pretty slow as well.   Just looking for the best/quickest way to work this. 

[JagdeeshN]

Hi @JavaGirl ,

In-Db tools are specially useful if you are doing all the processing within the same database. 

Please refer to this article below on when and how to use these tools.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/FAQ-How-Do-the-In-Database-tools-Wo...

I am happy to jump on a quick call to understand the use case better and discuss the possible technical options.

Best,

Jagdeesh

[danilang · Accepted solution]

Hi @JavaGirl 

I haven't tried this myself, but from the solution to this post, I think it might work.  Use a Formula In-DB with  row_number() over (order by [group_by_field1],[group_by_field2]  desc) to generate grouped RecordIDs.  Then Filter in-DB Where RecordID = 1  

Dan

[JavaGirl]

@danilang  Winner winner chicken dinner!  Thank you very much!  This is exactly what i wasn't sure how to accomplish.