0 Replies Latest reply: Mar 1, 2012 11:02 AM by B Y RSS

    Load excel file by column and translate by line

      Dear all,

       

       

      I would like to do something, but I don't know how.

       

       

      I have a Excel file, with structure :

      INVOICE_DATE;     INVOICE_NUMBER;     PERIOD_FROM;     PERIOD_TO;     PRICE;

      01.02.2012;             123456;                           20.11.2011;             31.01.2012;        50;

      10.02.2012;             456789;                           22.12.2011;             15.01.2012;        5000; 

       

       

      I need in my QVW file, to see the price by month, by year. I need to calculate a average price by day, and multiply with my number of day on the period for each month of each year.

       

       

       

      So, I would like to have a table with :

      INVOICE_DATE;     INVOICE_NUMBER;     YEAR;     MONTH;     PRICE;

      01.02.2012;             123456;                           2011;      11;             12;

      01.02.2012;             123456;                           2011;      12;             19;

      01.02.2012;             123456;                           2012;      01;             19;

       

       

      10.02.2012;             456789;                           2011;      12;             1875;

      10.02.2012;             456789;                           2012;      01;             3215;

       

       

       

       

      How can I do this by script ???

       

      At present I have this script :

       

      PRICE:

       

      LOAD

      [INVOICE_DATE],

      [INVOICE_NUMBER],

      [PERIOD_FROM],

      [PERIOD_TO],

      [PRICE],
      //right([au],4) - right([du],4) as nbannee,
      [PERIOD_TO] - [PERIOD_FROM] as NbDays,
      ceil ([PRICE] / ([PERIOD_TO] - [PERIOD_FROM])) as Price_by_Day
      FROM
      ..........

       

       

       

       

      Thanks in advance for your help.