Rank & Window Function
I wanted to create two static fields for two different tabels coming from WB.
1.One is ID- unique # for each row.
2.Source- which differentiate which data belongs to table A and Table B
eg : Union ALL both tabel A & B final o/p shall be :
ID| Product| Class| Source
1 |AAA | C | table A
2 |ABC | D | table A
3 |AAA | C | table A
5 |AAc | CC | table B
I am able to successfully add column ID in both tables A & B. How can I create Source column ?
Comments
-
Assuiming you are using the ETL, you should be able to use the row number function in the rank and window tile to create a unique ID for each row in your data. For #2, you should use the Add Constants tile and it after each dataset and before you union them together with the Append Rows tile.
Here's a link about the Add Constants feature: https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/02ETL_Actions%3A_Edit_Columns#Add_Constants
Here's a link about the Row Number:
**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.0 -
Thank you! can this b edone in one ETL flow ?
Like Add Constants and Rank & Window?
When I add these two on Table A, it gives me error .
0 -
Yes, generally it would look something like this. Just depends what else you want to do in your ETL.
You can build some pretty elaborate ETLs if you want to. It just depends on your needs. You may want to watch a couple videos on building ETLs if you aren't familiar with it. There is a lot you can do.
**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 -
When I try to select same input data set in second one to add ID, it does not let me. it greys out.- Im trying to follo wthe pic you shared, like I am unable to get Data combined Governance in two input data sets.
EG: Table A-----> Add Constants
Table A( its greyed out )-----------> Add constants 1
0 -
In my screenshot, the two dataset that are at the beginning are two different datasets. The names are cut off in my screenshot, so I could see how you were confused. For you, you should have Table A -> Add Constants and then Table B -> Add Constants 1 and then bring them together after that like the rest of the screenshot.
**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 -
So technically I cant do Ranking and adding constants in one single ETL.
As both my Table A & B needs ranking and adding constants.
0 -
Yes, that is correct.
**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 -
To avoid these 4 steps, ie. adding two static fields in each table A & B- Source, Ranking. Is there a way we can do it in sql ?
Please share .
0 -
Yes, you could certainly do that. If you are using Microsoft SQL Server, it would look like this:
SELECT ROW_NUMBER() OVER(ORDER BY Product ASC) AS ID, Product, Class, 'TableA' AS Source
FROM TableA
UNION
SELECT ROW_NUMBER() OVER(ORDER BY Product ASC) AS ID, Product, Class, 'TableB' AS Source
FROM TableBThe newly-created ID column is your unique row number for each row and the Source column is the equivalent of Add Constants. The UNION is the equivalent of the Append Rows tile that brings the data together. Here is a link to Microsoft's documentation on the ROW_NUMBER function.
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15
**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.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive