Making Impossible Possible.

    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:

    ComplexFile.png

    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

    license".

     

    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

    Reseller                          =

    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

     

     

    Account information:

    Salutation          = Mrs.

    Title               =

    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    =

    Phone               =

    Fax                 =

    E-Mail              = mg@ssssssfffff.com

    VAT ID              =

    Payment             = PayPal

    Registration name   = ssssssfffff

    Language            = We speak only JAVA here

     

    Additional information:

    The customer selected the following delivery type: electronic.

     

    We generated the following license key and sent it to the user:

    Dear ssssssfffff.

     

    Many thanks for buying Visual Importer Enterprise.

     

    Here is your software registration information:

     

    User name is: ssssssfffff

    Key is:

    00H015-AE2YB0-N2FH9Z-C7RY9A-7RZ021-JXKX7N-UU5Z13-2T4E43-76J8RF-M65K11

     

    ==== 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.

    WorkFlow.pngOur 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'

    Variables.png

    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

    Calculation.png

    Next step is to get rid of lines without '=' character in it. This can be easily done by using validator object:

    Contains.pngHow we can split the data using '=' as delimiter, than we get rid of spaces and remove USD prefix

    Transformer.png

    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.

    Pivot.png

    Last transformation: here we use our variables and map the to the output fields (transformation is very big so we show only relevant fields)

    Last Transformation.png

    The result:

    Result.png

    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.

    Mask.png

    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