1 Reply Latest reply: Dec 4, 2011 11:18 AM by Tanel Rüütli RSS

    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

        • Re: Qlikview ETL Query - How to create additional rows based on input table.
          Tanel Rüütli

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