-
Use of If-Then-Else logic in Aperture Data Studio
Imagine you are asked to use Aperture Data Studio to generate a new field for your sales data. You have the input fields: Discount Code, Quantity and Price. The ask is to generate a new field “Offer Price” using the following logic: How can you do this in Aperture Data Studio? The answer is to build a custom transformation…
-
Importing and Exporting Workflows
One of the very first things that you will probably wonder, is just how portable are my workflows that I create in Data Studio? The answer is simple. In v1.x if you have a workflow that you have created, there are some dependencies but as long as your source and target environments contain the same sources and…
-
Extracting titles and honorifics from names (advanced)
In this earlier post I described how you might want to extract titles or honorifics from a name string, this might be important for a marketing campaign where to you wish to personalize your communication by saying dear sir or madam. Unfortunately the data isn't always as we want it and sometimes we land up with names of…
-
Extract Title from Names
During the course of the data preparation that you might be doing on names, you might have occasion to pull out the Title or honorific associated with the name. In this example we assume all names have an honorific, if some don't you need to use conditional logic which i will cover separately. You have at least two…
-
Grouping in V2 and differences with V1
if you're working with financial data with Aperture Data Studio, one of the things that you might want to do, is to rollup columnar values and produce aggregates. When you produce aggregates you don't necessarily want to see all the columns or all the rows. Certainly if you see all the columns, the challenge can be…
-
Extracting the country from an unstructured address using a lookup list of countries
The problem that you may need to solve is that you have data that is effectively a contiguous string of unstructured data and you want to determine perhaps the country from that data in order to either Address Match or filter your records. A basic approach is to simply use an extract Matches function within the Transform…
-
Checking data like social security numbers (SSN) for correctness
Social Security Numbers, National Identity numbers and the like, on face value are simply strings of data, made up with letters or numbers and segmented with hyphens. There is however inherent intelligence in many of these identifiers and even check digits. You'll find regular expressions to validate these kinds of…
-
Checking data like social security numbers (SSN) for correctness
Social Security Numbers, National Identity numbers and the like, on face value are simply strings of data, made up with letters or numbers and segmented with hyphens. There is however inherent intelligence in many of these identifiers and even check digits. You'll find regular expressions to validate these kinds of…
-
Creating a new data connection to Salesforce from Data Studio via JDBC
It is likely that quite soon after you start to use Data Studio you will want to connect Data Studio to databases and systems of record. This is quickly and relatively easily done using the JDBC connector component delivered as a part of the application. Details of how to add a custom JDBC driver are explained in the…
-
Validate Customer data example Workflow
This simple validation workflow leverages the Sample Data of Customer V1, The FILTER step filters to just US Data The SAMPLE step only samples 1000 rows randomly (the randomization is done only once) The TRANSFORM steps replace _ with . for emails, reformat phones to the international standard and then generate boolean…
-
Joining Data example for Purchase Order Headers and Lines
This workflow uses PO sample data The PO header and rows, is joined using the order number This workflow performs a date calculation, adds an average aggregation based on a product ID grouping and then produces a reduced list of records sorted by average days to fulfil. You could consider this as a simple report workflow…
-
Refresh your sources
Occasionally you may find that the staged sources fall out of synch in how Data Studio renders them in the UI. If you feel this is the case, you can explicitly refresh the sources from the UI by right mouse clicking on the ☰ under the My Files area and selecting refresh
-
How to resolve a unmapped source on a workflow
In a previous tip I suggested you might want to export and import your workflow between systems. I also mentioned that one of the prerequisites for this to work seamlessly, was for you to have the same or similar configuration and sources defined in both systems. It will happen, that there are source differences. So how do…
-
How to request a license key
-
I cannot access any files I previously loaded through Data Explorer
Sometimes you will find that the view of files in the data explorer is out of sync with what is stored in the import folder. This can happen for a variety of reasons. You may have moved the location of the import folder or may have manually added or removed files from the import folder using a file system method rather…
-
Working with Dates
I have a set of data that contains some dates What I would like to do, is split the day, month and year into their own columns, this is easily done with the TRANSFORM step and using the EXTRACT TIMESTAMP ELEMENT date functions - there are many options available here If you want to determine the elapsed time between today…
-
How do you work out where the row comes from ?
When you're combining data from multiple data sources that potentially have duplicated data, you may want to work out which row you want to win. This is partiulcarly relevant when you use harmonization. You can base decisions on the source, or which records to prioritize against in grouping. You could consider this as a…
-
Solving for literal null values
Depending on the source that you are working with, you may find that you actually have literal nulls in your data, this is a common results from SQLServer sqlcmd exports to CSV. You'll be able to easily determine whether you have literal nulls by running a profile on your data, you may not easily detect this in the preview…
-
Dealing with character sets
Sometimes, the file that want to use with Data Studio will contain characters that would come into data studio incorrectly. Consider this file, countries.csv Note that in the Preview and configure the letter Å (å in lower case) an overring A is common in Swedish, Danish, Norwegian, Finnish, North Frisian, Walloon,…
