Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];