Rank & Window Function

Techy007 · November 2019

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 ?

MarkSnodgrass · November 2019

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 · November 2019

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 · November 2019

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 · December 2019

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 · December 2019

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 · December 2019

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 · December 2019

Yes, that is correct.

Techy007 · December 2019

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 · December 2019

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

Rank & Window Function

Comments

Categories