Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Making Impossible Possible.

etl_tools
Not applicable

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

Labels (1)
Attachments
Comments
Not applicable

It wasn't possible in QlikView because you asked a technical question to the SALES people. I know that helps presenting your product, but the conclusion is not correct.

rbecher
Not applicable

Hi,

it is much more easier to load this kind of files into QlikView if you KNOW how to use the QlikView Script language!

Ralf

Version history
Revision #:
1 of 1
Last update:
‎04-30-2012 11:23 AM
Updated by: