-
Goal: I want to create some Javascript that adds a new column to some data pulled from an SQL query that cleans up the information from a column in the input data called 'description' and outputs this new column together with the details from the orignal SQL query.
-
Steps: I have tried the following code "// JavaScript code for processing transactions data in Retool
// Define a function to manipulate the raw data
function processTransactionsData(rawData) {
// Check if rawData is an array, otherwise convert it
if (!Array.isArray(rawData)) {
rawData = Object.values(rawData);
}
// Helper function to clean the 'description' and output as 'BTRRefinedName'
function cleanName(name) {
if (typeof name !== 'string') {
return name;
}
// Remove dates like 19/06/19, 19-JUN-19
name = name.replace(/\b\d{1,2}[\/\-\.]?\d{1,2}[\/\-\.]?\d{2,4}\b/g, '');
// Remove dates like 19JUN19
name = name.replace(/\b\d{1,2}[A-Za-z]{3,}\d{2,4}\b/g, '');
// Remove Day and Month like 19JUN
name = name.replace(/\b\d{1,2}[\/\-\.]?[A-Za-z]{3,}\b/g, '');
// Remove Month and Year like JUN19
name = name.replace(/\b[A-Za-z]{3,}\d{2}\b/g, '');
// Remove Month and Year like JUN-19, JUN19, or 06/2019
name = name.replace(/\b[A-Za-z]{3,}[\/\-\.]?\d{2,4}\b/g, '');
// Remove words that contain both letters and numbers
name = name.split(' ').filter(word => {
return !( /[a-zA-Z]/.test(word) && /\d/.test(word) );
}).join(' ').trim();
// Remove numbers with more than 4 digits from anywhere in the string
name = name.replace(/\b\d{5,}\b/g, '');
// Remove excess whitespace
name = name.replace(/\s+/g, ' ').trim();
return name;
}
// Process each transaction to rename the first variable and add 'BTRRefinedName'
const processedData = rawData.map(transaction => {
// Create a shallow copy and rename the first key
const { user_id, ...rest } = transaction; // Assuming 'UserID' is the first field name in raw data
return {
D_One_User_ID: user_id, // Renamed first variable
...rest, // Keep remaining variables
BTRRefinedName: cleanName(transaction.description) // Add the cleaned name
};
});
return processedData;
}
// Access the raw data fetched from your query
const rawData = D_One_Raw_Tns_Fetch?.data ?? ;
// Ensure rawData is an array before processing
const rawDataArray = Array.isArray(rawData) ? rawData : Object.values(rawData);
// Process the data to get the transactions with refined names and renamed first variable
const processedTransactions = processTransactionsData(rawDataArray);
// Print the processed transactions
console.log(processedTransactions);
// Return processed data for further use within Retool
return processedTransactions;"
- Details: While I get an output from this it seems to only ever by the same information from the orignal SQL query but loosing the column names. What can I do to retain the orignal column names and to add the new column into the output?
