Replacing IFNull value based on a separate key

user07596
user07596 Member
in Magic ETL

Hello and thanks to anyone who responds to this in advance.

 

I'm relatively new to using DOMO and have been experiencing issues trying to replace null values with the corresponding data. The report that I am using was exported in a weird manner due to how our back end is structured. The primary key (Project ID) exports 2 rows for every value and the column (name) has a null value (every other row). View below for reference. 

 

example.PNG

 

My goal:

I would ultimately like to have FirstNameLastName + Supplier ID  sit on one row per project ID (so replace if null and delete duplicate rows (which I will do through ETL).

 

 

Tagged:

Best Answer

  • Unknown
    Unknown
    Answer ✓

    Hi, @user07596,

     

    Welcome to Domo!

     

    My recommendation is to think of your single dataset as two separate datasets: one, which has the project Name field populated; and the other, which has the supplied ID field populated. In Magic ETL, you can breakt your single dataset into two datasets using a couple of filter tiles:

    • Drag an Input tile onto the canvas and configure it by connecting it to your source dataset
    • Drag two Filter tiles onto the canvas and connect the Input tile to each one
    • Configure the first Filter tile to exclude rows where supplier ID is not null
      • The result of this filter will be only the rows that have values in the Name column
    • Configure the second Filter tile to exclude rows where name is not null
      • The result of this filter will be only the rows that have values in the supplier ID column
    • Drag a Join tile onto the canvas and connect both Filter tiles to the Join tile
    • Configure the Join tile by selecting the input data sources and by specifying the joining columns as Project ID column. Depending on your data, I would probably suggest using an Inner join. 
    • You can preview and test your results at this point by clicking the "Preview" button at the top of the canvas.
    • You can then check your results coming out of any tile by clicking that tile and then clicking "Preview".
    • If you preview the output of the Join tile, you'll see each column is represented twice. That's because it takes all the columns from both of the sources being joined together. You can clean up that output using a "Select Columns" tile.

     

    Hope that points you in the right direction.

     

    -Dan

     

Answers

  • Unknown
    Unknown
    Answer ✓

    Hi, @user07596,

     

    Welcome to Domo!

     

    My recommendation is to think of your single dataset as two separate datasets: one, which has the project Name field populated; and the other, which has the supplied ID field populated. In Magic ETL, you can breakt your single dataset into two datasets using a couple of filter tiles:

    • Drag an Input tile onto the canvas and configure it by connecting it to your source dataset
    • Drag two Filter tiles onto the canvas and connect the Input tile to each one
    • Configure the first Filter tile to exclude rows where supplier ID is not null
      • The result of this filter will be only the rows that have values in the Name column
    • Configure the second Filter tile to exclude rows where name is not null
      • The result of this filter will be only the rows that have values in the supplier ID column
    • Drag a Join tile onto the canvas and connect both Filter tiles to the Join tile
    • Configure the Join tile by selecting the input data sources and by specifying the joining columns as Project ID column. Depending on your data, I would probably suggest using an Inner join. 
    • You can preview and test your results at this point by clicking the "Preview" button at the top of the canvas.
    • You can then check your results coming out of any tile by clicking that tile and then clicking "Preview".
    • If you preview the output of the Join tile, you'll see each column is represented twice. That's because it takes all the columns from both of the sources being joined together. You can clean up that output using a "Select Columns" tile.

     

    Hope that points you in the right direction.

     

    -Dan

     

  • Unknown
    Unknown

    PS: NullIf/Case/Replace statements won't work in this case. That's because those functions operate on a single row of data. What you're trying to do is collapse data from multiple rows of data. Thus, I believe the Filter/Join method I suggested will be your best bet.

  • user07596
    user07596 Member

    This was successful. Thank you for your help Dan! 

Categories