What is ETL (Extract Transformation Load)

ETL term stands for the Extract, Transform and Load (ETL) and which is explained in different levels. ETL is a process of data warehousing and which is responsible for extract data out of the (RAW) source systems and placing it into a data warehouse QVD. ETL involves the following tasks:

Extracting the data:-

Extracting the data from raw source systems (ERP, SAP, FLAT FILES(TXT, EXCEL, CSV), other oprational systems), data from different raw source systems is converted into one Consolidated Data Model in warehouse format which is ready for transformation processing.

 

Transforming the data:-

Transforming the data may involve the following tasks:

  •   Applying business rules  (so-called derivations, e.g., calculating new measures and dimensions),
  •   Cleaning (e.g., Date fields, Currency Conversion, finding Sum and Count metrics, Mapping NULL to 0 or Date Calculations etc.),
  •   Cleaning or Filtering (e.g., selecting only certain columns to load),
  •   Splitting a column into multiple columns and vice versa,
  •   Joining together data from multiple sources (e.g., Joins, Concatenation, Lookup, Merge),
  •   Transposing rows and columns,
  •   Applying any kind of simple or complex data validation (e.g., Loading manipulated or calculated row are empty then reject the row from processing.

Loading the data:-

Loading the data into a QVW application is same as loading data warehouse or data repository to other applications.

About the Peek functions

peek(fieldname [ , row [ , tablename ] ] )

Returns the contents of the fieldname in the record specified by row in the internal tabletablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbersindicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label, see Table Labels, without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Examples:

peek( ‘Sales’ )
returns the value of Sales in the previous record read ( equivalent to previous(Sales) ).

peek( ‘Sales’, 2 )
returns the value of Sales from the third record read from the current internal table.

peek( ‘Sales’, -2 )
returns the value of Sales from the second last record read into the current internal table.

peek( ‘Sales’, 0, ‘Tab1′

 

This Post Is Tagged

Interval Match for Time Dimension

Friends lets more discussed about the Interval Match function with an little bit simple example. In many times we have the information is available in the different tables and when we need some information which is not in the proper way. But we need the report from that tables and dimension.

Have a look with simple example of the Order table and the Event generated table that is information of the orders and the event generation.

If we look around the Order log table structure that carries the fields named  Start , End & Order. Wherever start is the order start time and End is Order End time and the Order carries the Order Name and it looks like

OrderLog:
LOAD * Inline
[
Start , End , Order
01:00 , 03:35 , A
02:30 , 07:58 , B
03:04 , 10:27 , C
07:23 , 11:43 , D

];

And the Event Log structure carries Time  , Event & Comment. Wherever the Time has information of the Event generation and for this time what is Event number or name here we have Event number and same like for Comment we have details that which event has the information that is what event has do on that time means comments. And structure of the table looks like

EventLog:
LOAD * Inline
[
Time  , Event , Comment
00:00 , 0 , Start of shift 1
01:18 , 1 , Line stop
02:23 , 2 , Line restart 50%
04:15 , 3 , Line speed 100%
08:00 , 4 , Start of shift 2
11:43 , 5 , End of production

];
So here first we load this tables in the Qlikview and we need the report like that event time is in which range in the OrderLog table.

1. Let we see for the Event 1 and compare it with the OrderLog table that comes in Order A in 01:00 and 03:35 Start and End time.

2. Like ways if we check for Event 3 for compare with the OrderLog so that is come under Order B and Order C also because the start and End times is in between the 02:30to 07:58 for Order B and for Order C it is in 03:04 to 10:27. So there are many event like ways are come under the different orders.

So for this we have a interval match table with script

Data:
IntervalMatch( Time )  LOAD Start,End Resident OrderLog;

And the complete for report for the
Time, Event, Comment, Order, Start, End