1

How would I modify the following MuleSoft Transform Message to reverse the concatenation of the EMPLOYEE_CODE field?

Background: I am working with a MuleSoft process that (i) queries data from a database using a 'SELECT' component (ii) then transforms it into a specified .csv format using a series of 'Transform Message' components and (iii) deposits the .csv onto a SFTP server.

One of the data fields (EMPLOYEE_CODE) has data that comes from the DB in this format: 01-1111 (two characters before a '-' and four characters after). I need my MuleSoft Transform message to swap the order of this separation in the EMPLOYEE_CODE field to output it in this format: 1111-01 (latter four characters now before the '-' and the initial two characters after the '-') before generating the .csv file.

Current MuleSoft Transform Message:

%dw 2.0
output application/csv
quoteValues=true
---
payload map {
    employeeID: $.EMPL_ID,
    firstName: $.FIRST_NAME,
    lastName: $.LAST_NAME,
    employeeCode: $.EMPLOYEE_CODE
}

For references, here's an example Data Table that I'm receiving from DB:

EMPL_ID FIRST_NAME LAST_NAME EMPLOYEE_CODE
0000001 John Doe 01-1111
0000002 Rick James 02-2222
0000003 John Smith 03-3333

Transform Message needs to change this to (1111-01, 2222-02, and 3333-03).

For reference, here's the SQL Query in my Select:

SELECT
   EMPL_ID
   FIRST_NAME
   LAST_NAME
   EMPLOYEE_CODE
FROM DATABASE.TABLE
David Boutin
  • 15
  • 4

3 Answers3

2

It looks you only need to update the field EMPLOYEE_CODE with a very basic string manipulation (concatenate substrings in a different order). I used the update operator to resolve it transforming the data before your script. The other fields are irrelevant for this solution.

%dw 2.0
output application/java
---
payload map (
    $ update {
        case code at .EMPLOYEE_CODE -> code[3 to -1] ++ "-" ++ code[0 to 1]
    }
)

If you prefer to integrate with your script just replace this line:

          employeeCode: $.EMPLOYEE_CODE[3 to -1] ++ "-" ++ $.EMPLOYEE_CODE[0 to 1]
aled
  • 21,330
  • 3
  • 27
  • 34
  • Thanks very much, I'm new to MuleSoft so this really helps. For anyone curious, if there is an employee who does not have an EMPLOYEE_CODE, the script would be to be written as follows to accommodate: ``` employeeCode: if($.EMPLOYEE_CODE == " ") $.EMPLOYEE_CODE else $.EMPLOYEE_CODE[3 to -1] ++ "-" ++ $.EMPLOYEE_CODE[0 to 1] ``` – David Boutin Apr 01 '22 at 03:11
1

Another approach with some inbuilt String functions:

%dw 2.0
import * from dw::core::Strings
output application/json
var inp = "01-1111"
---
substringAfter(inp,"-")++"-"++ substringBefore(inp,"-")

Alternate approach using pure string manipulation

%dw 2.0
import * from dw::core::Strings
output application/json
var inp = "01-1111"
---
rightPad(rightPad(substringAfter(inp,"-"),5,"-"),6,substringBefore(inp,"-"))

In either case you can replace inp with $.EMPLOYEE_CODE

Salim Khan
  • 4,233
  • 11
  • 15
0

A more generic approach:

StringUtils.dwl:

%dw 2.0

/**
* Reverse the tokens position of a delimited string.
*
* === Parameters
*
* [%header, cols="1,13"]
* |===
* | Name | Type | Description
* | `delimitedString` | `String \| Null` | An optional string that contains a delimiter.
* | `delimiter` | `String` | The delimiter.
* |===
*
* === Example
*
* This example reverse the string "01-1111" into "1111-01"
*
* ==== Source
*
* [source,DataWeave,linenums]
* ----
* %dw 2.0
* output application/json
* ---
* reverseTokens("01-1111", "-")
* ----
*
* ==== Output
*
* `Expression Output`
*
* [source,XML,linenums]
* ----
* "1111-01"
* ----
*/
fun reverseTokens(delimitedString : String | Null, delimiter : String) = do {
    var tokens = (delimitedString splitBy delimiter ) default []
    ---
    tokens[-1 to 0] joinBy delimiter
}

main.dwl:

%dw 2.0
output application/csv quoteValues=true
import StringUtils
---
payload map {
    employeeID: $.EMPL_ID,
    firstName: $.FIRST_NAME,
    lastName: $.LAST_NAME,
    employeeCode: StringUtils::reverseTokens($.EMPLOYEE_CODE, '-')
}
Adrian Kamycki
  • 121
  • 3