Rank & Window Function

Techy007

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 ?

Find more posts tagged with

Comments

MarkSnodgrass

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:

https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window#Row_Number

Techy007

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 .

MarkSnodgrass

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.

Techy007

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

MarkSnodgrass

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.

Techy007

So technically I cant do Ranking and adding constants in one single ETL.

As both my Table A & B needs ranking and adding constants.

MarkSnodgrass

Yes, that is correct.

Techy007

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 .

MarkSnodgrass

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 TableB

The 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