How to create DW Script using DW Script

· Introduction
∘ Sample CSV
∘ Transformation Script
∘ Output for Sample CSV
· Implementation in real-time
· Customisation

Introduction

Imagine, your client has sent you an excel/csv file with source fields which you’ll be receiving when called an API or from some source system. Now, in the same excel we have target fields. All of them are direct mapping so no transformations are needed.

Title Banner

For 10 to 15 fields it’s easy to do it manually. But, if they are more you can simply use the following script to create DW Mapping payload.

Sample CSV

SourceField,TargetField
Service_ID,serviceId
Market,market
Dealer_Cd,dealerCode
Repair_Order_Number,repairOrderNumber
Repair_Order_Open_Date,repairOrderOpenDate
Repair_Order_Close_Date,repairOrderCloseDate
Odometer_In,odometerIn
Odometer_Out,odometerOut
Actual_Labor_Hours,actualLaborHours
Billed_Labor_Hours,billedLaborHours
RO_Total_Price_Calculated,roTotalPriceCalculated
Customer_Price,customerPrice
Internal_Price,internalPrice
Warranty_Price,warrantyPrice
Labor_Price,laborPrice
Customer_Labor_Price,customerLaborPrice
Internal_Labor_Price,internalLaborPrice
Warranty_Labor_Price,warrantyLaborPrice
Parts_Price,partsPrice
Customer_Parts_Price,customerPartsPrice
Internal_Parts_Price,internalPartsPrice
Warranty_Parts_Price,warrantyPartsPrice
RO_Total_Cost_Calculated,roTotalCostCalculated
Customer_Cost,customerCost
Internal_Cost,internalCost
Warranty_Cost,warrantyCost
Labor_Cost,laborCost
Customer_Labor_Cost,customerLaborCost
Internal_Labor_Cost,internalLaborCost
Warranty_Labor_Cost,warrantyLaborCost
Parts_Cost,partsCost
Customer_Parts_Cost,customerPartsCost
Internal_Parts_Cost,internalPartsCost
Warranty_Parts_Cost,warrantyPartsCost

Transformation Script

%dw 2.0
output text
---
write(payload map (
    ($."TargetField") : "payload." ++ $."SourceField"
) reduce ($$ ++ $)) replace "\"payload." with "payload.\""

or you can use this below ️ link to directly access the pre-defined DW Playground. ⬇️⬇️⬇️⬇️

DataWeave Interactive Learning Environment

Output for Sample CSV

This can be directly used as DW Script in the Transform Message or Set Payload or any other expression component.

{
  serviceId: payload."Service_ID",
  market: payload."Market",
  dealerCode: payload."Dealer_Cd",
  repairOrderNumber: payload."Repair_Order_Number",
  repairOrderOpenDate: payload."Repair_Order_Open_Date",
  repairOrderCloseDate: payload."Repair_Order_Close_Date",
  odometerIn: payload."Odometer_In",
  odometerOut: payload."Odometer_Out",
  actualLaborHours: payload."Actual_Labor_Hours",
  billedLaborHours: payload."Billed_Labor_Hours",
  roTotalPriceCalculated: payload."RO_Total_Price_Calculated",
  customerPrice: payload."Customer_Price",
  internalPrice: payload."Internal_Price",
  warrantyPrice: payload."Warranty_Price",
  laborPrice: payload."Labor_Price",
  customerLaborPrice: payload."Customer_Labor_Price",
  internalLaborPrice: payload."Internal_Labor_Price",
  warrantyLaborPrice: payload."Warranty_Labor_Price",
  partsPrice: payload."Parts_Price",
  customerPartsPrice: payload."Customer_Parts_Price",
  internalPartsPrice: payload."Internal_Parts_Price",
  warrantyPartsPrice: payload."Warranty_Parts_Price",
  roTotalCostCalculated: payload."RO_Total_Cost_Calculated",
  customerCost: payload."Customer_Cost",
  internalCost: payload."Internal_Cost",
  warrantyCost: payload."Warranty_Cost",
  laborCost: payload."Labor_Cost",
  customerLaborCost: payload."Customer_Labor_Cost",
  internalLaborCost: payload."Internal_Labor_Cost",
  warrantyLaborCost: payload."Warranty_Labor_Cost",
  partsCost: payload."Parts_Cost",
  customerPartsCost: payload."Customer_Parts_Cost",
  internalPartsCost: payload."Internal_Parts_Cost",
  warrantyPartsCost: payload."Warranty_Parts_Cost"
}

Implementation in real-time

Let’s see the sample JSON payload and response that can be generated by the script.

{
  "Service_ID": "765545644565",
  "Market": "Brazil",
  "Dealer_Cd": "3107BZ90 ",
  "Repair_Order_Number": "6016268",
  "Repair_Order_Open_Date": "2023-07-20",
  "Repair_Order_Close_Date": "2023-08-22",
  "Odometer_In": "1",
  "Odometer_Out": "3",
  "Actual_Labor_Hours": "2.4",
  "Billed_Labor_Hours": "2.4",
  "RO_Total_Price_Calculated": "326.56",
  "Customer_Price": "300",
  "Internal_Price": "309.22",
  "Warranty_Price": "17.34",
  "Labor_Price": "314",
  "Customer_Labor_Price": "24",
  "Internal_Labor_Price": "297.5",
  "Warranty_Labor_Price": "16.5",
  "Parts_Price": "12.56",
  "Customer_Parts_Price": "0",
  "Internal_Parts_Price": "11.72",
  "Warranty_Parts_Price": "0.84",
  "RO_Total_Cost_Calculated": "52.79",
  "Customer_Cost": "51",
  "Internal_Cost": "52.19",
  "Warranty_Cost": "0.6",
  "Labor_Cost": "43.5",
  "Customer_Labor_Cost": "11",
  "Internal_Labor_Cost": "43.5",
  "Warranty_Labor_Cost": "9",
  "Parts_Cost": "9.29",
  "Customer_Parts_Cost": "2",
  "Internal_Parts_Cost": "8.69",
  "Warranty_Parts_Cost": "0.6"
}

The above payload has to be transformed to another JSON with target fields.

Transforming using generated Script

{
  "serviceId": "765545644565",
  "market": "Brazil",
  "dealerCode": "3107BZ90 ",
  "repairOrderNumber": "6016268",
  "repairOrderOpenDate": "2023-07-20",
  "repairOrderCloseDate": "2023-08-22",
  "odometerIn": "1",
  "odometerOut": "3",
  "actualLaborHours": "2.4",
  "billedLaborHours": "2.4",
  "roTotalPriceCalculated": "326.56",
  "customerPrice": "300",
  "internalPrice": "309.22",
  "warrantyPrice": "17.34",
  "laborPrice": "314",
  "customerLaborPrice": "24",
  "internalLaborPrice": "297.5",
  "warrantyLaborPrice": "16.5",
  "partsPrice": "12.56",
  "customerPartsPrice": "0",
  "internalPartsPrice": "11.72",
  "warrantyPartsPrice": "0.84",
  "roTotalCostCalculated": "52.79",
  "customerCost": "51",
  "internalCost": "52.19",
  "warrantyCost": "0.6",
  "laborCost": "43.5",
  "customerLaborCost": "11",
  "internalLaborCost": "43.5",
  "warrantyLaborCost": "9",
  "partsCost": "9.29",
  "customerPartsCost": "2",
  "internalPartsCost": "8.69",
  "warrantyPartsCost": "0.6"
}

Customisation

• You can modify script to parse keys from 2 different JSONs and create the script.
• You can modify script to add default key word as well.