Excel - New Column/Calcuation - Load Script

[#1]

Hello Everyone.

I am attempting to add new field/column that is calcuation based from an excel file in the load script.

The calcuation i am seeking to undertake is as following;

**Days Worked = Current Date - Hired Date, Where Hired Date is part of the Excel File and Current Date should be derived based on load date or current system date

***Years Worked = Current Date - Hired Date, Convert the Days Worked to Year e.g 365 Days Worked would be 1 Year

Following is my Excel Load Script:

LOAD EmpID as EmployeeID,
     [Last Name],
     [First Name],
     Title,
     [Hire Date],
     Office,
     Extension,
     [Reports To],
     [Year Salary],
     Combin ([First Name],[Last Name])As Name
  
FROM

(biff, embedded labels, table is Employee$);

LOAD Office,
     Address,
     [Postal Code],
     City,
     StateProvince,
     Phone,
     Fax,
     Country
FROM

(biff, embedded labels, table is Office$);

As you can see from above a very simple script, however I am looking to learn where & how to insert this calcuation.

I am using Qlikview 11.

I searched relevant Qliview blogs and support community, however I was unable to locate this information.

Again, this should be a very easy process.

Your help and guidance is welcomed.

Regards

Uday

[jonathandienst]

Hi

This should do what you want:

LOAD EmpID as EmployeeID,

     [Last Name],

     [First Name],

     Title,

     [Hire Date],

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     [First Name] & [Last Name] As Name,

     Today() - [Hire Date] As DaysWorked,

     Floor((Today() - [Hire Date])/365) As YearsWorked

FROM

(biff, embedded labels, table is Employee$);

Regards

Jonathan

[er_mohit]

convert your hire date format with qlikview date format using

date and date # function like this suppose your date format is DD/MM/YYYY and convert it into DD-MM-YYYY

Try  this

load *,

Today() - [Hire_Date] As DaysWorked,

     Floor((Today() - [Hire_Date])/365) As YearsWorked ;

LOAD EmpID as EmployeeID,

     [Last Name],

     [First Name],

     Title,

     date(date#([Hire Date],'DD/MM/YYYY'),'DD-MM-YYYY') as Hire_Date,

     Office,

     Extension,

     [Reports To],

     [Year Salary],

     [First Name] &' '& [Last Name] As Name

     FROM

(biff, embedded labels, table is Employee$);

hope it helps