Connector & DataSet Issue

KJRowe6

Hi

 

I am using an out of the box Connector for my ticket data but the only two options are Append and Replace, I firstly pulled down 365 days and now each day I either have the option to do an append or replace, if I replace I lose 365 days data and left with 24 hours so I use Append (API limits mean I couldn't do a total update every 24hrs for 365 days of data).  CountDistinct works in most cases but unfortunately there are some fields which are changed in the life an incident so for example, if an incident was changed to a Service Request mid-life (a type field) then it would have one entry for Incident and 1 for SR which gives me incorrect data.

 

Would there be a way of sorting my dataset using ETL so that each time an update is done my duplicates are removed please?  Issue is with this case is that it would need to remove the oldest duplicate because the update has occurred and the type changed.

 

Kind Regards

Kev

jaeW_at_Onyx

@KJRowe6  this is a super common question in the Dojo!  

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Append-only-missing-rows/td-p/49014

Have a look at this post.

 

Basically you need a recursive query paired with a window function to identify and remove duplicates in Magic!

KJRowe6

Thanks JaeW, I need to digest this a bit more as my first reaction was YIKES but will sit down tonight and give it a whirl.  Think this one might be beyond my current skill level, but all in good time! 

jaeW_at_Onyx

Kev,

Here's the official Domo KB. 

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_ETL_DataFlow

 

Start with that.  The notes I've added guarantee that you always get 'the right row' but their implementation is probably good enough, and certainly will get you started.

 

If you and your team need support with Domo implementations, I do provide consulting services at a very competitive hourly or day rate, AND I've got tons of videos in my youtube channel (linked in my signature).