Connector & DataSet Issue

Reply
Highlighted
White Belt

Connector & DataSet Issue

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

Highlighted
Major Red Belt

Re: Connector & DataSet Issue

@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!


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
White Belt

Re: Connector & DataSet Issue

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! 

Highlighted
Major Red Belt

Re: Connector & DataSet Issue

Kev,

Here's the official Domo KB. 

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

 

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

 

 


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.