Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

Excel - New Column/Calcuation - Load Script


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

2 Replies
Highlighted
MVP
MVP

Re: Excel - New Column/Calcuation - Load Script

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Honored Contributor II

Re: Excel - New Column/Calcuation - Load Script

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