topic Re: Excel - New Column/Calcuation - Load Script in QlikView

Excel - New Column/Calcuation - Load Script

Fri, 20 Sep 2013 05:37:15 GMT

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

Re: Excel - New Column/Calcuation - Load Script

jonathandienst — Fri, 20 Sep 2013 05:55:40 GMT

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

Re: Excel - New Column/Calcuation - Load Script

er_mohit — Fri, 20 Sep 2013 06:05:24 GMT

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 ;