{"id":109133,"date":"2026-04-14T14:04:00","date_gmt":"2026-04-14T14:04:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=109133"},"modified":"2026-04-14T07:56:05","modified_gmt":"2026-04-14T07:56:05","slug":"data-manipulation-techniques-in-esproc-spl-a-complete-guide","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/python\/data-manipulation-techniques-in-esproc-spl-a-complete-guide\/","title":{"rendered":"Data manipulation techniques in esProc SPL: a complete guide"},"content":{"rendered":"\n

In this article, part of the “Moving from Python to esProc”<\/a> series, we’ll look at esProc SPL’s<\/a> data manipulation capabilities compared to Python<\/a>.<\/strong><\/p>\n\n\n\n

As you may already know, real-world data rarely arrives in a clean, analysis-ready format. You’ll frequently need to clean messy data<\/a>, reshape it to suit your analysis needs, merge multiple datasets, and perform complex filtering and calculations before extracting meaningful insights. Luckily, esProc SPL<\/a> handles these common data manipulation tasks with remarkable elegance and efficiency.<\/p>\n\n\n\n

How to clean ‘messy’ data with esProc SPL<\/h2>\n\n\n\n

Datasets are rarely perfect. Missing values<\/a>, inconsistent formats, and outliers can significantly impact analysis quality. Let’s look at how esProc SPL handles these data cleaning<\/a> challenges – often some of the most time-consuming aspects of data analysis.<\/p>\n\n\n\n

How to identify and handle missing values in esProc SPL<\/h3>\n\n\n\n

Missing values occur when data is incomplete. You need to detect and handle them to ensure accurate results. This flowchart illustrates the systematic approach to dealing with missing data in a dataset:<\/p>\n\n\n\n

\"A<\/figure>\n\n\n\n

It begins with loading raw data, then moves to identifying missing values through counting and visualization techniques. Once missing values are detected, the process branches into three strategic options: removing records with missing values (which is simple but can lose valuable information), replacing missing values with defaults or calculated substitutes (like means or medians), or computing new values derived from other fields in the dataset. <\/p>\n\n\n\n

All three strategies ultimately converge to produce a clean dataset ready for analysis. This flowchart provides a structured decision framework that helps data analysts choose the most appropriate method for handling missing values based on their specific data context and analysis requirements.<\/p>\n\n\n\n

The first step in cleaning messy data is identifying missing values. In esProc SPL, missing values are represented as `null`. Let’s start by creating a sample dataset with some missing values:<\/p>\n\n\n\n

 <\/td>      A<\/td> <\/td><\/tr>
     1<\/td>=file(“document\/sales.csv”).import@ct()    <\/td>Sales data with 100 rows  <\/td><\/tr>
     2<\/td>=A1.run(AMOUNT=if(rand()<0.1,null,AMOUNT))<\/td>Randomly set ~10% of AMOUNT values to null  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now, let’s identify rows with missing values:<\/p>\n\n\n\n

   3<\/td>=A2.select(AMOUNT==null)   <\/td>Rows with missing AMOUNT  <\/td><\/tr>
   4<\/td>=A3.len()                <\/td> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The output of A4 will show that we have 10 rows with missing AMOUNT values, which is approximately 10% of our dataset, as expected. Now, let’s count missing values by region:<\/p>\n\n\n\n

    5<\/td>=A2.groups(REGION; count(AMOUNT==null):MISSING_COUNT, count(AMOUNT):TOTAL_COUNT)   <\/td>Count missing values by region  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the distribution of missing values across regions. Now, let’s handle these missing values using different techniques:<\/p>\n\n\n\n

How to remove rows with missing values<\/h4>\n\n\n\n
    6<\/td>=A2.select(AMOUNT!=null)  <\/td>Remove rows with missing AMOUNT  <\/td><\/tr>
    7<\/td>=A6.len()                <\/td> <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The output of A7 shows the number of rows we have after removing the rows with missing AMOUNT values.<\/p>\n\n\n\n

How to fill missing values with a constant<\/h4>\n\n\n\n
      8<\/td>=A2.derive(ifn(AMOUNT, 0):AMOUNT_FILLED)   <\/td>Replace nulls with 0  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

How to fill missing values with statistical measures<\/h4>\n\n\n\n

You can first add the AMOUNT_FILLED field to A2, then fill values directly after grouping. This avoids using new and join, improving performance. This is an SPL-exclusive technique using retained grouped subsets.<\/p>\n\n\n\n

    9<\/td>=A2.derive(:AMOUNT_FILLED) =A2.derive(:AMOUNT_FILLED)  <\/td><\/tr>
   10<\/td>=A9.group(REGION)  <\/td><\/tr>
   11<\/td>=A10.run(a=~.avg(AMOUNT),~.run(AMOUNT_FILLED=ifn(AMOUNT,a)))  <\/td><\/tr>
  12<\/td>=A9.to(5)  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

A9: Add an empty AMOUNT_FILLED field to A2.<\/p>\n\n\n\n

A10: Group A9 by REGION and retain original record sets within each group. Updating field values in the grouped records will modify the corresponding values in the original table A9.<\/p>\n\n\n\n

A11: Iterate through A10. For each group, calculate the average of AMOUNT and store it in a temporary variable a. Then, iterate through the group\u2019s records and assign values to AMOUNT_FILLED. ifn(AMOUNT,a) means: return AMOUNT if it is not null; otherwise, return a.<\/p>\n\n\n\n

\n
\n
\n
\n

Simple Talk is brought to you by Redgate Software<\/h3>\n
\n Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI. <\/div>\n <\/div>\n Discover how Redgate can help you<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

How to detect and handle outliers in esProc SPL<\/h3>\n\n\n\n

Outliers<\/a> can significantly skew your analysis and lead to incorrect conclusions. Let’s explore how to detect and handle outliers in esProc SPL.<\/p>\n\n\n\n

How to identify outliers using Z-score<\/h4>\n\n\n\n

The Z-score measures how many standard deviations a data point is from the mean. Values with a Z-score greater than 3 or less than -3 are often considered outliers. Since A14 only contains one row, there\u2019s no need to join with A6. You can directly add the Z_SCORE field to A6 and return the final result. The code is as follows:<\/p>\n\n\n\n

14<\/td>=A6.group(;~.avg(AMOUNT):AVG, sqrt(var@r(~.(AMOUNT))):STD)   <\/td>Calculate mean and standard deviation<\/td><\/tr>
15<\/td>=A6.derive((AMOUNT-A14.AVG)\/A14.STD: Z_SCORE)                                <\/td>Join with statistics<\/td><\/tr>
16<\/td>=A15.select(abs(Z_SCORE)>2)         <\/td>Calculate Z-score<\/td><\/tr>
17<\/td>=A16.top(-5, Z_SCORE)                  <\/td>Select outliers (Z-score > 2)  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the top outliers based on Z-score. The first two rows have Z-scores greater than 2, indicating they are potential outliers.<\/p>\n\n\n\n

How to identify outliers using IQR (interquartile range)<\/h4>\n\n\n\n

Another common method for detecting outliers is the IQR<\/a> method, which identifies values below Q1 – 1.5*IQR or above Q3 + 1.5*IQR as outliers:<\/p>\n\n\n\n

     18<\/td>=A6.groups(;median(1:4,AMOUNT):Q1,median(3:4,AMOUNT):Q3)  <\/td>Calculate Q1 and Q3<\/td><\/tr>
   20<\/td>A6.select(AMOUNT<A21.LOWER_BOUND || AMOUNT>A21.UPPER_BOUND)     <\/td>Select outliers<\/td><\/tr>
  21<\/td>=A20.top(-5,AMOUNT)                                 <\/td>First 5 outliers<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

How to handle outliers<\/h4>\n\n\n\n

Once you’ve identified outliers, you can handle them in several ways, as shown in this flowchart:<\/p>\n\n\n\n

\"An<\/figure>\n\n\n\n

Let’s implement the capping strategy:<\/p>\n\n\n\n

   22<\/td>=A18.derive(AMOUNT_CAPPED:if(AMOUNT>UPPER_BOUND,UPPER_BOUND,if(AMOUNT<LOWER_BOUND,LOWER_BOUND,AMOUNT)))    Cap outliers Replace the nested if with:
if(AMOUNT>UPPER_BOUND:UPPER_BOUND, AMOUNT<LOWER_BOUND:LOWER_BOUND; AMOUNT)
The full line should be:
=A22.derive(if(AMOUNT>UPPER_BOUND:UPPER_BOUND, AMOUNT<LOWER_BOUND:LOWER_BOUND; AMOUNT):AMOUNT_CAPPED:)  <\/td><\/tr>
  23<\/td>=A22.select(DATE,REGION,PRODUCT,AMOUNT,AMOUNT_CAPPED)    Select relevant columns  <\/td><\/tr>
  24<\/td>=A23.to(5)                             <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Since our dataset doesn’t have extreme outliers based on the IQR criterion, the capped values are the same as the original values.<\/p>\n\n\n\n

How to remove duplicates in esProc SPL<\/h3>\n\n\n\n

Duplicate records<\/a> can skew your analysis and lead to incorrect conclusions. Let’s explore how to identify and remove duplicates in esProc SPL:<\/p>\n\n\n\n

  29<\/td>=A1.groups(REGION+”-“+PRODUCT:DUPLICATE_KEY;count():COUNT)     <\/td>Count occurrences of each key<\/td><\/tr>
  30<\/td>=A30.select(COUNT>1)                         <\/td>Select keys with multiple occurrences  <\/td><\/tr>
  31<\/td>=A31.to(5)                                 <\/td>First 5 duplicate keys<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This example simply counts the number of records with the same REGION and PRODUCT. Now, let’s remove duplicates based on a specific key:<\/p>\n\n\n\n

   33<\/td>=A29.groups(REGION,PRODUCT:max(AMOUNT):MAX_AMOUNT)    <\/td>Keep only the record with the maximum AMOUNT for each key  <\/td><\/tr>
   34<\/td>=A33.to(5)                                 <\/td>First 5 deduplicated records  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the deduplicated records, keeping only the record with the maximum AMOUNT for each REGION-PRODUCT combination.<\/p>\n\n\n\n

How to reshape data in esProc SPL: wide to long and vice-versa<\/h2>\n\n\n\n

Data reshaping is a common operation in data analysis, especially when preparing data for visualization or specific analytical techniques. Let’s explore how esProc SPL handles data reshaping operations.<\/p>\n\n\n\n

The wide to long format (unpivoting)<\/h3>\n\n\n\n

Let’s start by creating a wide-format dataset with quarterly sales by region:<\/p>\n\n\n\n

   35<\/td>=A34.groups(REGION,year(DATE):YEAR,ceil(month(DATE)\/3):QUARTER;sum(AMOUNT):TOTAL_SALES)  <\/td>Calculate total sales by region, year, and quarter<\/td><\/tr>
   36<\/td>=A35.pivot(REGION; QUARTER,TOTAL_SALES; 1:”Q1″,2:”Q2″,3:”Q3″, 4:”Q4″)       <\/td>Pivot to create quarterly columns<\/td><\/tr>
   37<\/td>=A36.to(5)                              <\/td> First 5 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the total sales by region and quarter in a wide format, with each quarter as a separate column. Now, let’s convert this wide format back to a long format:<\/p>\n\n\n\n

   38<\/td>=A36.pivot@r(REGION; QUARTER,TOTAL_SALES; Q1:1,Q2:2,Q3:3,Q4:4)    <\/td>             Unpivot quarterly columns<\/td><\/tr>
   39<\/td>=A38.select(SALES)                    <\/td>Remove rows with null sales<\/td><\/tr>
   40<\/td>=A39.to(10)                              <\/td>First 10 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

After pivoting, the data order remains the same as the original, so no additional sorting is required. The data is displayed in a long format, with each row representing the sales for a specific region, year, and quarter. This format is often more suitable for visualization and certain types of analysis.<\/p>\n\n\n\n

The long to wide format (pivoting)<\/h3>\n\n\n\n

Now, let’s create a different long-format dataset and convert it to a wide format:<\/p>\n\n\n\n

  41<\/td>=A38.groups(PRODUCT,month(DATE):MONTH;sum(AMOUNT):TOTAL_SALES)    <\/td>Calculate total sales by product and month  <\/td><\/tr>
  42<\/td>=A41.to(10)                               <\/td>First 10 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

When grouping, the data is automatically sorted by the grouping fields, so no additional sorting is needed. This shows the total sales by product and month in a long format. Now, let’s convert this to a wide format:<\/p>\n\n\n\n

    43<\/td>=A38.pivot(PRODUCT;MONTH,TOTAL_SALES; 1:”JAN”,2:”FEB”,3:”MAR”,4:”APR”,5:”MAY”,6:”JUN”,7:”JUL”,8:”AUG”,9:”SEP”,10:”OCT”,11:”NOV”,12:”DEC”)       <\/td>Pivot to create monthly columns<\/td><\/tr>
   44<\/td>=A43.to(5)                                <\/td>First 5 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the total sales by product and month in a wide format, with each month as a separate column. This format is often more suitable for reporting and certain types of analysis.<\/p>\n\n\n\n

How to merge and join datasets in esProc SPL<\/h2>\n\n\n\n

Joining datasets is a fundamental operation in data analysis, allowing you to combine information from multiple sources. SPL provides several methods for joining datasets, similar to SQL joins.<\/p>\n\n\n\n

Let’s create a second dataset with customer information. Use the ‘create’ function to define the table structure and record to populate data, as shown below:<\/p>\n\n\n\n

    A45<\/td>=create(CUSTOMER,INDUSTRY,SIZE,COUNTRY).record(   [“TechCorp”,”Technology”,”Large”,”USA”,   “HomeOffice”,”Retail”,”Small”,”Canada”,   “DataSystems”,”Technology”,”Medium”,”USA”,   “EduCenter”,”Education”,”Medium”,”UK”,   “CloudNet”,”Technology”,”Large”,”Germany”,   “OfficeMax”,”Retail”,”Large”,”USA”,   “HealthCare”,”Healthcare”,”Medium”,”Canada”,   “FinServices”,”Finance”,”Large”,”UK”,   “GovAgency”,”Government”,”Large”,”USA”,   “SmallBiz”,”Retail”,”Small”,”Germany”] )    <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now, let’s look at the different types of joins:<\/p>\n\n\n\n

The inner join<\/h3>\n\n\n\n

An inner join returns only the rows that have matching values in both datasets:<\/p>\n\n\n\n

  46<\/td>=join(A1:o,CUSTOMER; A51:c,CUSTOMER)                     <\/td>Inner join with customer data<\/td><\/tr>
  47<\/td>=A46.to(5)                                <\/td>First 5 rows  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the sales data enriched with customer information including industry, size, and country.<\/p>\n\n\n\n

The left join<\/h3>\n\n\n\n

A left join returns all rows from the left dataset and the matching rows from the right dataset. Compared to the previous example, just add the @1 option to indicate a left join. Note: it\u2019s the number \u201c1\u201d, not the letter \u201cl\u201d:<\/p>\n\n\n\n

   48<\/td>=join@1(A1:o,CUSTOMER; A51:c,CUSTOMER)  <\/td><\/tr>
   49<\/td>=A48.new(o.DATE, o.CUSTOMER, o.PRODUCT, o.AMOUNT, c.INDUSTRY, c.SIZE, c.COUNTRY)  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

In this case, the output is the same as the inner join because all customers in the sales data have matching records in the customer data.<\/p>\n\n\n\n

The full join<\/h3>\n\n\n\n

A full join returns all rows when there is a match in either the left or right dataset:<\/p>\n\n\n\n

   50<\/td>=join@f(A1:o,CUSTOMER; A51:c,CUSTOMER)                  <\/td>Full join with customer data<\/td><\/tr>
  51<\/td>=A50.to(15)                               <\/td>First 15 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The output might include rows from both datasets, even if there’s no match.<\/p>\n\n\n\n

Joining on multiple columns<\/h3>\n\n\n\n

You can also join datasets on multiple columns:<\/p>\n\n\n\n

   52<\/td>=A1.groups(CUSTOMER,month(DATE):MONTH;sum(AMOUNT):TOTAL_SALES)   <\/td>Calculate total sales by customer and month<\/td><\/tr>
   53<\/td>=A52.to(10)                              <\/td>First 10 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now, let’s create another dataset with monthly targets:<\/p>\n\n\n\n

  A54<\/td>=create(CUSTOMER,MONTH,TARGET).record(   [“TechCorp”,4,1500,   “TechCorp”,5,2000,   “TechCorp”,6,1800,   “TechCorp”,7,1500,   “HomeOffice”,4,500,   “HomeOffice”,5,600,   “HomeOffice”,6,550,   “HomeOffice”,7,450,   “DataSystems”,4,2000,   “DataSystems”,5,1800,   “DataSystems”,6,1600,   “DataSystems”,7,1400] )    <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now, let’s join the sales data with the targets data on both CUSTOMER and MONTH:<\/p>\n\n\n\n

  55<\/td>=join(A54:o, CUSTOMER, MONTH; A65:c, CUSTOMER, MONTH)              <\/td>Join on CUSTOMER and MONTH<\/td><\/tr>
  56<\/td>=A55.to(10)                              <\/td>First 10 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the sales performance against targets for each customer and month.<\/p>\n\n\n\n

\n
\n
\n
\n

Enjoying this article? Subscribe to the Simple Talk newsletter<\/h3>\n
\n Get selected articles, event information, podcasts and other industry content delivered straight to your inbox. <\/div>\n <\/div>\n Subscribe now<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

More esProc SPL filtering techniques and conditions<\/h2>\n\n\n\n

esProc SPL offers filtering capabilities that allow you to select rows based on conditions. Let’s explore some more filtering techniques.<\/p>\n\n\n\n

How to filter with multiple conditions in esProc SPL<\/h3>\n\n\n\n
   57<\/td>=A1.select(REGION==”East” && (PRODUCT==”Laptop” || PRODUCT==”Server”) && AMOUNT>1000)      filtering<\/td><\/tr>
   58<\/td>=A57.to(5)                                First 5 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows high-value sales (over $1,000) of laptops or servers in the East region.<\/p>\n\n\n\n

How to filter with regular expressions in esProc SPL<\/h3>\n\n\n\n
    59<\/td>=A1.select(like(CUSTOMER,”*Corp*”))        Filter customers with “Corp” in the name<\/td><\/tr>
    60<\/td>=A60.to(5)                                 First 5 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows sales to customers with “Corp” in their name.<\/p>\n\n\n\n

How to filter with date ranges in esProc SPL<\/h3>\n\n\n\n
     61<\/td>=A1.select(DATE>=date(“2023-05-01”) && DATE<=date(“2023-05-31”))    Filter for May 2023<\/td><\/tr>
     62<\/td>=A61.to(2)                                 =First 2 rows<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The output of A62 shows sales in May 2023.<\/p>\n\n\n\n

How to filter with subqueries in esProc SPL<\/h3>\n\n\n\n

esProc SPL allows you to use the results of one query to filter another query, similar to subqueries in SQL:<\/p>\n\n\n\n

  63<\/td>=A1.groups(CUSTOMER;sum(AMOUNT):TOTAL_PURCHASES)   Calculate total purchases by customer<\/td><\/tr>
  64<\/td>=A63.select(TOTAL_PURCHASES>5000)           Select high-value customers<\/td><\/tr>
  65<\/td>=A64.to(2)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

Now, let’s use these high-value customers to filter the original sales data:<\/p>\n\n\n\n

  66<\/td>=A1.select(A65.(CUSTOMER).contain(CUSTOMER))   Filter for high-value customers<\/td><\/tr>
  67<\/td>=A66.sort(CUSTOMER,DATE)                     Sort by customer and date<\/td><\/tr>
  68<\/td>=A67.to(5)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows sales to high-value customers (those with total purchases over $5,000), sorted by customer and date.<\/p>\n\n\n\n

How to filter with window functions in esProc SPL<\/h3>\n\n\n\n

Window functions allow you to perform calculations across a set of rows related to the current row. Let’s use window functions to filter for sales that are above the average for their region:<\/p>\n\n\n\n

   69<\/td>=A1 .group(REGION; (AVG_REGION_AMOUNT =~.avg(AMOUNT), ~.select(AMOUNT >AVG_REGION_AMOUNT)):ABOVE).conj(ABOVE)  <\/td><\/tr>
   70<\/td>=A69.sort(REGION,AMOUNT:-1)                 Sort by region and amount<\/td><\/tr>
   71<\/td>=A70.to(10)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows sales that are above the average for their region, sorted by region and amount.<\/p>\n\n\n\n

How to create calculated fields and derived columns in esProc SPL<\/h2>\n\n\n\n

Calculated fields and derived columns allow you to create new data based on existing columns. SPL’s `derive` method provides a way to create these columns.<\/p>\n\n\n\n

Basic calculations<\/h3>\n\n\n\n
   72<\/td>=A1.derive(TAX:AMOUNT*0.1,TOTAL:AMOUNT+AMOUNT*0.1)  Add tax and total columns<\/td><\/tr>
   73<\/td>=A72.to(5)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the original sales data with added tax (10% of the amount) and total (amount + tax) columns.<\/p>\n\n\n\n

Conditional calculations<\/h3>\n\n\n\n
   74<\/td>=A1.derive(DISCOUNT:if(AMOUNT>1000,AMOUNT*0.05,0),NET_AMOUNT:AMOUNT-if(AMOUNT>1000,AMOUNT*0.05,0))  Add discount and net amount columns<\/td><\/tr>
   75<\/td>=A74.to(5)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the original sales data with added discount (5% for amounts over $1,000) and net amount (amount – discount) columns.<\/p>\n\n\n\n

Date calculations<\/h3>\n\n\n\n
  76<\/td>=A1.derive(interval(DATE,now()):DAYS_SINCE_ORDER, DATE+7:DELIVERY_DATE)    Add days since order and delivery date columns<\/td><\/tr>
  77<\/td>=A76.to(5)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the original sales data with added columns for days since order (assuming today is March 16, 2024) and delivery date (7 days after the order date).<\/p>\n\n\n\n

String manipulations<\/h3>\n\n\n\n
   78<\/td>=A1.derive(upper(CUSTOMER):CUSTOMER_UPPER,if(like(PRODUCT,”Lap*”):”Computer”,like(PRODUCT,”Ser.*”:”Server”,like(PRODUCT,”Mon.*”:”Peripheral”:”Other”):PRODUCT_CATEGORY)   Add uppercase customer and product category columns<\/td><\/tr>
   79<\/td>=A78.to(5)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the original sales data with added columns for uppercase customer names and product categories based on the product name.<\/p>\n\n\n\n

Calculated columns with window functions<\/h3>\n\n\n\n
    80<\/td>=A1.sort(REGION,AMOUNT:-1).derive(rank(AMOUNT;REGION):REGION_RANK)  <\/td><\/tr>
   81<\/td>=A80.sort(REGION,DATE).derive(cum(AMOUNT):RUNNING_TOTAL)  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the original sales data with added columns for running total (cumulative sum of AMOUNT by REGION, sorted by DATE) and rank (rank of AMOUNT within REGION, with higher amounts having lower ranks).<\/p>\n\n\n\n

How window functions and rolling calculations work in esProc SPL<\/h2>\n\n\n\n

Window functions allow you to perform calculations across a set of rows related to the current row. SPL provides powerful window functions for various analytical tasks.<\/p>\n\n\n\n

Ranking functions<\/h3>\n\n\n\n
  82<\/td>=A1.sort(AMOUNT:-1).derive(rank(AMOUNT):RANK, ranki(AMOUNT):DENSE_RANK, #:ROW_NUMBER)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

This shows the sales data with added ranking columns:<\/p>\n\n\n

\n