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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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 

  • 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
    MarkSnodgrass Portland, Oregon 🟤

    Yes, generally it would look something like this. Just depends what else you want to do in your ETL. 

    rank.PNG

    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.

  • 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
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • 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
    MarkSnodgrass Portland, Oregon 🟤

    Yes, that is correct.

  • 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
    MarkSnodgrass Portland, Oregon 🟤

    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