Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.

Not applicable

Qlikview ETL Query - How to create additional rows based on input table.

Hii All,

I am new to qlikview and am not familiar with the ETL process in Qlikview.  I have a query on the Data Transformation aspects of Qlikview.

I have a table that have the following fields.

Veh_id, Vehicle_Start_Date, Vehicle_End_Date, Premium

120, 15-dec-2011, 14-dec-2012, 250

121, 01-jan-2012, 31-dec-2012, 300

During the data load process, I want to create another table that stores the following information

Year_month, veh_id, Earned_Days, Earned_Premium

2011-12, 120, 16, 11

2012-01, 120, 31, 21

2012-02, 120 ,29, 20

.....

2012-01,121,31, 25

2012-01,121,29, 24

.....

This table stores the number of days a vehicle was active during the month along with the premium earned during this period.

So for each record in the base table, there can be around 12 records to be created in the new table.  One record for each month a vehicle was active.

Could you guys pls let me know how I should script the creation of a new table with this information during the load process.

Thanks

Jibu

1 Reply
tanelry
Contributor II

Re: Qlikview ETL Query - How to create additional rows based on input table.

You can use iterNo() to generate records.

Below example creates one record for each date between start and end and then aggregates to month. Note that it is just one load statement having 4 preceding load stages (read from bottom up).

I personally would leave the facts at date basis for better analytics (w/o aggregation in script), but since you asked...

Veh_service:

LOAD // aggregate

Veh_id,

Year_month,

sum(Earned_Days) as Earned_Days,

sum(Earned_Premium) as Earned_Premium

Group By Veh_id, Year_month

;
LOAD // add month dimension

*,

text(Date(Date,'YYYY-MM')) as Year_month

;

LOAD // create a record for each date:

Veh_id,

Premium / (Vehicle_End_Date - Vehicle_Start_Date) as Earned_Premium,

1 as Earned_Days,

date(Vehicle_Start_Date+(iterNo()-1)) as Date

WHILE iterNo() <= Vehicle_End_Date - Vehicle_Start_Date + 1

;

LOAD // just to convert the date strings

Veh_id,

Date#(Vehicle_Start_Date,'DD-MMM-YYYY') as Vehicle_Start_Date,

Date#(Vehicle_End_Date,'DD-MMM-YYYY') as Vehicle_End_Date,

Premium

;

LOAD * INLINE [

Veh_id, Vehicle_Start_Date, Vehicle_End_Date, Premium

120, 15-dec-2011, 14-dec-2012, 250

121, 01-jan-2012, 31-dec-2012, 300

];