Help Desk

Submit a ticket My Tickets
Welcome
Login

Find and Replace involving Multiple Columns input

I have to do a find and replace that is dependent on information from additional columns. 

The use case is for rows where my Department column is not populated. To this end, I want to be able to update a specific department based on the account type in the row. ie Balance Sheet accounts go to one dept, Revenue to another and so on. 

My assumption is I could easily accomplish the goal in Advanced Query with a reference table,  but I am trying to minimize use of code of any kind.  As an alternate, I envision the following:

  1. Create a copy of the Account column
  2. Strip it to the first column to assess type
  3. Merge with the Dept column
  4. Use Regex Find and Replace to map the blanks to the appropriate department
  5. Use Regex Find and Replace to clean up the extraneous account info

I would group all of the commands to highlight that it is all one step, but I think it is a lot of steps and might not be clear.

At the end of the day, I am looking for the best practice with minimal coding, to transform a column based on input from two or more columns.

For the above use case, here is some sample data:

DeptAccount
XYZ1200
WXY2200

1300

1200
ABC4500

4400

4500
ABA4600


The data needs to look like this:

DeptAccount
XYZ1200
WXY2200
ZZZ1300
ZZZ1200
ABC4500
AAA4400
AAA4500
ABA4600


Because Balance sheet accounts go to ZZZ, and Revenue Accounts go to AAA. 

  • I am  continuing to work the problem. As I anticipated the multiple non-coded steps would be more time consuming at run time, especially if I have large data sets, I went with the Advanced Query, and ran into an interesting problem. 

    I have a step that prefaces the dept with 'Dept_' wherever I find the proper data in the column. I also created a table that equates the first digit of the account to the desired department. But the query I wrote wouldn't work. The short version of the query I was working with is:

    Select 
       CASE substr(Department,1,5) WHEN 'Dept_' THEN Department 
       ELSE 'Dept_' || (
             select Dept from DeptLookup where AcctType = substr([Account],6,1)
             ) 
    END as Dept from DataTable

    It reported a series of errors around the case statement keywords and the Department field itself. I also tried the alternate case syntax:

    Select 
       CASE WHEN substr(Department,1,5) = 'Dept_' THEN Department 
       ELSE 'Dept_' || (
             select Dept from DeptLookup 
             where AcctType = substr([Account],6,1)
             )
    END as Dept from DataTable

    But that didn't work either, so I thought it might be an error in my subquery, so I reduced it to: 

    Select 'Dept_' || (
          select Dept from DeptLookup 
          where AcctType = substr([Account],6,1)
          ) 
    as Dept from DataTable

    And that worked fine, so I poked around and I poked around, and finally by changing the query to:

    Select CASE 
       WHEN substr(Department,1,5) = 'Dept_' THEN Department 
       ELSE 'Dept_' || (
             select Dept from DeptLookup 
             where AcctType = substr([Account],6,1)
             ) 
    END as Dep from DataTable

    I was finally able to get the result I wanted. It seems the same field name being used in the subquery and for the calculated field was the issue, even though it wasn't a problem without the case statement.

  • Hi Mike - Thank you for sharing your thoughts with the solution. That being said, your SQL syntax in the last statement that you provided above has a number of syntax issues as it relates to the sample data you provided. Would you be able to correct the syntax and prove that it works before we accept your answer?

    Thank you in advance.

    OneCloud Support

Login to post a comment