Establishing a consistent ID using Rank and Window
Hi,
With using MagicETL i wanted to understand if it possible to enforce a consistent ID where only net new rows would receive a new ID associated to it
Currently i have a dimension table that contains two columns agency_name and advertiser_name where in the cases of agency_name being unknown it is assigned a unique identifier "unknownar[numberical string]" (i.e unknownar00001). I am using a combination of rank and window and formula (using concat) to create this identifier. Noting that this table only contains the distinct unique combinations of agency_name and advertiser_name
The rank and window function is set to order by advertiser name ascending. Given that my current ETL recreates this table everyday, what will happen is that if a net new agency_name + advertiser_name combination is introduced where lets say the advertiser_name string has a higher alphabetical sorting, the "unknown" identifier that is being applied will change given the new rank/order.
This causing complexity with a downstream process in which we are mapping that unknown identifier to the intended "agency_name" and effectively cause a situation where previously mapped values will need to be remapped.
Is there a way to enforce a consistent ID using magic etl ?
Best Answer
-
You could potentially implement a recursive dataflow that would allow you to look for new records and then assign them new ids while keeping the ids that have already been created. I think an easier and more sustainable solution is to use your concactenation of the agency and advertiser names as your unique identifier downstream. This would hold up better when new agency and advertiser combinations come in.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
You could potentially implement a recursive dataflow that would allow you to look for new records and then assign them new ids while keeping the ids that have already been created. I think an easier and more sustainable solution is to use your concactenation of the agency and advertiser names as your unique identifier downstream. This would hold up better when new agency and advertiser combinations come in.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive