Fill in Blanks based on existing column

Options

Hi All,

Financial Analyst trying to tackle ETLs. Vaguely familiar and can work my way through MagicETL but am stumped on this one. Hoping for some help.

I have a data set replicated below. I want to fill the the [BLANK] using an ETL so on the data set where the store numbers are the same and the ticketIDs are the same, the category fills in the blank.

Layout below.

Store TicketID Category

1 123ABC Internet

1 123ABC [BLANK]

1 456WYZ Telephone

1 456WYZ [Blank]

2 111UWC Internet

2 111UWC [Blank]

2 789QWE Telephone

2 78QWE [Blank]

Can anyone point me in the right direction?

Thanks!

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    If the category is the same across for each store and ticket I’d you can use a rank and window tile to get the max value of the category for a new column and then use an alter column tile to drop the old category field and rename your new category field

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    If the category is the same across for each store and ticket I’d you can use a rank and window tile to get the max value of the category for a new column and then use an alter column tile to drop the old category field and rename your new category field

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    Options

    Alternatively you could filter the rows for non null values, feed that into a remove duplicates based on your two ids and the use a join tile to join them back together based on the id fields and then rename the new category

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**