We have delivered a lot of data transformation and integration projects and one day we realized, that we need to put something on the end-user computer, which allows editing lookup tables, running reports and performing calculations. Our users did not want to run SQL plus or Microsoft SQL Server Management Studio. It is just too complex for them.
This is how Active Table Editor was born.
All our ETL tools have no limitations, which means you can load your data today and solve all the data quality problems right now.
Our WIKI has more detailed information if you are stuck post your question on our support forum and we will do our best to assist you
Please contact us if you need help with transforming the data
Note: transformation is used to prepare data for the range lookup transformation function
IP2Location™ is a non-intrusive geo IP solution to help you to identify visitor's geographical location, i.e. country, region, city, latitude, longitude, ZIP code, time zone, connection speed, ISP and domain name, IDD country code, area code, weather station code and name, mobile carrier information, elevation & usage type<br>using a proprietary IP address lookup database and technology without invading the Internet user's privacy.
Source file example
Here is an XML example, it is very well structured therefore there not need to perform additional transformations.
<Table><Record><ID>1</ID><Company>James Bond Production</Company><Amount>13</Amount></Record><Record><ID>2</ID><Company>Green Cloud</Company><Amount>14</Amount></Record></Table>
James Bond Production
However there is no guarantie that next file will have exactly the same structure, for example this XML has a different tags order.
<Table><Record><Company>James Bond Production</Company><ID>1</ID><Amount>13</Amount></Record><Record><ID>2</ID><Company>Green Cloud</Company><Amount>14</Amount></Record></Table>
This XML has additional YEAR tag
<Table><Record><ID>1</ID><Company>James Bond Production</Company><Year>1956</Year><Amount>13</Amount></Record><Record><ID>2</ID><Company>Green Cloud</Company><Amount>14</Amount></Record></Table>
This kind of of problems can be easily addressed by using XSLT:
<?xml version=“1.0” encoding=“UTF-8”?><xsl:stylesheet version=“1.0” xmlns:xsl=“http://www.w3.org/1999/XSL/Transform”><xsl:output method=“xml” indent=“yes” version=“1.0”/><xsl:template match=“Table”><Table><xsl:for-each select=“Record”><Record><ID><xsl:value-of select=“ID”/></ID><Company><xsl:value-of select=“Company”/></Company><Amount><xsl:value-of select=“Amount”/></Amount></Record></xsl:for-each></Table></xsl:template></xsl:stylesheet>
Note: XSLT is a way of transforming XML into a different format
Press magnifying glass button to amend the XSLT transformation
Here is an XML example, all the data we need is stored as XML attribute so we need to convert into simpler XML format
<Table><Record><CompanyData ID="1" Company="James Bond Production" Amount="13"></CompanyData></Record><Record><CompanyData ID="2" Company="Green Cloud" Amount="146"></CompanyData></Record></Table>
Here is XSLTtransformation:
<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="xml" indent="yes" version="1.0"/><xsl:template match="Table"><Table><xsl:for-each select="Record"><Record> <ID><xsl:value-of select="CompanyData/@ID"/></ID><Company><xsl:value-of select="CompanyData/@Company"/></Company><Amount><xsl:value-of select="CompanyData/@Amount"/></Amount></Record></xsl:for-each></Table></xsl:template></xsl:stylesheet>
Note: XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents or formats
<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:output method="text" encoding="UTF-8"/><xsl:template match="Table"><xsl:text>ID,Company,Amount</xsl:text><xsl:text>
</xsl:text><xsl:for-each select="Record"><xsl:value-of select="ID"/><xsl:text>,</xsl:text><xsl:value-of select="Company"/><xsl:text>,</xsl:text><xsl:value-of select="Amount"/><xsl:text>
Notes: We kept XSLT simple sot to is easy to understand and modify if necessary.
The transformation uses XML XSLT Transformation Function to convert XML to CSV and it also changes file name extension
Quite often our user's want to trigger package execution from their own software. This can be easily done by adding a record to the QUEUE table, We have created a number of helpers stored procedures for Oracle, SQL Server PostgreSQL and MySQL. (Download source code)
Here is an SQL Server example:
CREATE PROCEDURE [dbo].[EXECUTE_OBJECT]( @ComputerName NVARCHAR(255) ='', @LogDirectory NVARCHAR(255) ='', @Platform NVARCHAR(255) ='', @User NVARCHAR(255) ='', @ObjectId Int, @UseAgent Int = 1 -- 1 = true, 0 = false)AS BEGIN BEGIN TRAN-- Calculating IDDECLARE @ID Intselect @ID =max(ID)+1 from ID_GENERATOR where id_type=2update ID_GENERATOR set ID=@ID where id_type=2INSERT INTO QUEUE( QUEUE_ID, OBJECT_ID, OBJECT_TYPE, OBJECT_NAME, LOG_FILE, SUBMITED, STATUS, USE_AGENT, COMPUTER_NAME, APPLICATION_EXECUTED_PLATFORM, OSUSER)SELECT @ID as QUEUE_ID, OBJECT_ID, OBJECT_TYPE, NAME as OBJECT_NAME, @LogDirectory+'\Package_'+Cast(@ID as VARCHAR)+'.log' as LOG_FILE, getdate() as SUBMITED, 'P' as STATUS, @UseAgent as use_agent, @ComputerName as computer_name, @Platform as application_executed_platform, @User as osuserfrom objects_treewhere object_id=@ObjectIDCOMMITENDGO
EXEC EXECUTE_OBJECT 'DBSLCOMPUTER','C:\Logs','Windows (32-bit)','John',126,1
Log Directory Location
To access options dialogue, click System Menu→ File→ Options:
In the previous example, we demonstrated how to transform multiple XML files into CSV. Using a transformer gives the users maximum flexibility, however not all users require such functionality. For them, we offer XSLT Transformation Package Action.
Steps to follow
Hyper is Tableau's in-memory Data Engine technology optimized for fast data ingest and analytical query processing on large or complex data sets.
A QVD (QlikView Data) file is a file containing a table of data exported from QlikView. QVD is a native QlikView format.
Inconsistent values: US and USA from a human point of view are the same but for computers they are different. This can happen when merging data from different data sources
Missing values: UK and Germany values are missing. Most likely this data is incorrect and must be removed from the final dataset.
Data entry errors: Spain and SPain are two different values
Uniqueness: ORDER_ID must be unique
Inconsistent Date Formats: It is a common problem when merging data from various countries
Non-numeric characters inside numeric fields:Same as above, can be easily corrected using delete characters transformation function
Leading and trailing spaces:Invisible enemy of a data analyst. Use trim transformation function to correct this error
Data reader loads Excel file into memory, validator rejects rows with empty Country name field.
Once bad records are rejected the transformer performs additional cleaning
Here is how it works:
To view the example follow the steps below
Double click on transformation and select 0013 Extract IP Addresses
Characters can be removed using the "Delete Characters" transformation function or the "Keep characters" transformation function. The shorter the "Characters to Delete" string is the better is the performance. The same applies to the "Characters to Delete" string. This is only relevant to working with very large datasets. For small datasets, the difference is minor.
There are multiple ways to achieve the desired result. in this example, we are using the "Previous value transformation function"
Note: Source changed function forces creation of new file
There are other ways of transforming the data please contact us if you need help.
Advanced ETL Processor allows using multiple ways of transforming the data, which one to choose depends on the customer requirement. Using calculation transformation usually is the slowest option
Steps to follow:
Example 1: using compare values transformation function
Example 2: using calculations
Example 3: using lookup
Example 4: using If Equal to the transformation function
Page 1 of 12