Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

Community Browser