I went to Qonnections 2012 this year. One of the presentations was dedicated to making sales demos. I really liked it and once it was finished I had a...
I went to Qonnections 2012 this year. One of the presentations was dedicated to making sales demos. I really liked it and once it was finished I had a chat with US QlikView sales people. I told them that I can make their presentation even better. Than I did show them this file:
And I asked them: So how can I load it into QlikView?
The answer was: it is not possible.
I said, well our ETL-tools QlikView connector can load this data into QlikView, in fact it can load data from 20 different data sources and make extremely complex data transformations while loading data into QlikView. And you can include this into your demo.
I this article I am going to demonstrate how we process our orders every day
Here is what we receive from our payment provider ShareIt(Digital river).
==== START ====
Dear Mr./Mrs. . .,
We received an order for your product "Visual Importer Enterprise - single
We have received payment in full.
We sent out the license key of the program to the user:
Program = 120293736
Number of licenses = 1
ShareIt Ref # = 4MM912065
Promotion name =
Net sales = USD 330.00
Net Discount = USD 0.00
collected VAT = USD 0.00
Shipping = USD 0.00
VAT on Shipping = USD 0.00
Total = USD 330.00
Salutation = Mrs.
Last Name = Maria
First Name = Gates
Company = small soft foundation
Street = Blah 5B
ZIP = 12345
City = Megatown
FullCity = Big data capital
Country = Soft land
State / Province =
E-Mail = firstname.lastname@example.org
VAT ID =
Payment = PayPal
Registration name = ssssssfffff
Language = We speak only JAVA here
The customer selected the following delivery type: electronic.
We generated the following license key and sent it to the user:
Many thanks for buying Visual Importer Enterprise.
Here is your software registration information:
User name is: ssssssfffff
==== END =====
We would like to extract user details and KEY information and load it into QlikView dashboard. That looks like a complex operation but not really, the trick here is to split the transformation into small parts.
Our file format is fixed width 255 characters. If we look carefully at the data we will see that all relevant data has '=' character in it except 'User name' and 'Product Key'
When Previous Value equals to 'Key Is:' current line is Product key the script below saves it into <KEY> variable. Similar principle apply to the user name: when first characters are equal to User name is: the rest of the line is user name
Next step is to get rid of lines without '=' character in it. This can be easily done by using validator object:
How we can split the data using '=' as delimiter, than we get rid of spaces and remove USD prefix
The result of this transformation is pairs Filed name Value, we need to turn this into one line and we use for it pivot transformation.
Last transformation: here we use our variables and map the to the output fields (transformation is very big so we show only relevant fields)
But what if we sell thousands of licenses per day like QlikView does? That is simple: just save all the files into one directory use mask to process them in one go.
But we receive all our orders via e-mail how can we save them into the files?
Well we have a solution for it as well: Advanced ETL Processor can save all emails messages into the files.
Atached is the actual transformation. Unzip it into C:\support filder and open ats file