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

Sat, 03 Dec 2011 05:11:02 GMT

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

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

tanelry — Sat, 03 Dec 2011 22:27:07 GMT

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
];

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

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

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