Introduction to Smart Data Wrangling
With the smart wrangling capabilities in SAP Analytics Cloud, you can get immersed in gaining insights from your data through stories and analysis, without worrying about getting the data prepared perfectly from the start.
In this blogpost I will show the new Custom Expression Editor and how it works. All features shown in this blog are available with the QRC.Q3. My dataset is based on sales data. They contain information about products, sales representatives, cities, date, customer satisfaction, etc. I would like to perform three modeling steps on the dataset:
- Change data in one column based on the value of a second column
- Split string and keep only a part
- Perform calculations across multiple columns and create a new column
But first I would like to talk about the new design of the Data Wrangler.
The Details panel on the right side has been restructured. Here you can now switch columns between measures and dimensions simply by dragging and dropping. A particular added value is offered here if you switch from the “Dataset Overview” to the “Details” view.
To start with our tasks, we open the Expression Editor.
The help panel on the right-hand side will also open immediately. Here, especially new users will find a good introduction to the functions.
All functions are categorized. In the five categories you will find a detailed description of the syntax and functionality of each function, as well as examples that you can use directly.
Our first task is to modify data in one column based on another column. In our example this means that we get a new employee for the city of Liverpool and have to enter “John Doe” as manager for the column. For this we select the if-function.
As you can see, the system directly gives the correct syntax and we can enter our modification.
The command is to be read as follows: We assign a new value to the “Manager” column using the If query. If the city equals Liverpool, we insert “John Doe”. If the city is not Liverpool, we keep the old value.
By confirming the command, the expression is applied to the “Manager” column and the new values are inserted.
In our second tasks we use the split function. In our example, the “Category” column contains product descriptions. However, some columns contain several descriptions separated by a comma. We only want to keep the first description of an enumeration.
For this we define the comma as separator and the number of elements taken over – one, the first.
Calculation of a new column
The third task is to calculate a bonus for our managers. This is calculated from the columns “Sale Revenue” and “Customer Statisfaction”. If the sales revenue is greater than 20000 and customer satisfaction is equal to or greater than 90%, a bonus of 5000 should be paid. This payment should be stored in the new column “Bonus”.
As you can see, you do not need to create new columns separately. They can be defined using the editor and are created when the statement is executed. Another special feature is that we use the and-connector in the if-statement. With linking connectors, calculations can be performed across multiple columns.
After executing the expression, you can see that the dataset is extended by a new column.
The Custom Expression Editor is just one of the features introduced with the Smart Wrangler features. Together with the other features, Smart Wrangling offers extensive modeling possibilities to flexibly respond to the circumstances of data analysis.
blog and this Guided Playlist. An overview of all features and enhancements of the QRCQ3 release can be found in the Product Updates.
For more information about Smart Wrangling I recommend this Blog and this Guided Playlist. An overview of all features and enhancements of the QRC.Q3 release can be found in the Product Updates.
Nice blog. Thanks for sharing ??
Where are the functions of this 'custom expression editor' documented?
I could not find it anywhere in SAP help documentation.
I'd like to create a column with the current loading date/time.
Why is there no today() or now() function?
Hi, you're right. On the help page you will find quite limited information at the moment. For example here under point 9.
I would advise you to check the information directly in SAP Analytics Cloud. The help panel of the Custom Expression Editor gives you many explanations and examples of the functions.
Furthermore I can recommend our Guided Playlist on the topic of Smart Wrangling. There you can also find some background information.
Regarding your question how to create a column with the import date: There is the function now(). With this function you can easily create a new column with the current date:
Why is smart wrangling not available in an acquired model? It really confuses me when to use a model, dataset, import data directly in a story, and so on. They all have it's pro's and cons, but why don't you just create one artefact? Would make life a lot easier. Usecase: import data, transform it with custom expression editor and schedule a daily refresh. What would you suggest?
I'm sorry I'm so late replying. I can understand you completely. Product development is also aware of this problem. That's why we have great news!
With the Q2 2021 QRC, the New Model will be introduced. A revamped version of the "classic" data model. Take a look at the items in the Road Map Explorer. In the next releases, the New Model will be continuously enhanced, so that we can hopefully solve all problems soon. You can also take a look at the information in the product plan. There you will also find the opportunity to register for a webinar on 31 March. In this webinar, the New Model will be presented in detail.
I hope I was able to help you!
Thanks for your reply. I accidentally registered myself yesterday for that webinar, So i'm looking forward to it. And now lets hope it's not yet another modeltype.. 🙂
www.sapanalytics.cloud URL is invalid now. unfortunately, all the useful links in the article are missing.
can SAP SAC community website fix this?