Sum in the script

[#1]

Hi,
please, give a small advice for a beginner and qlikview-selflerner.

I would like to make a sum in the script, where I load data about employees and their working hours. And my target is to have a monthly sum of HoursWorked for each Employee . I can't figure out, how to do it, also my date is comming in "m/d/yyyy 12:00:00 AM" format. Database have records for every employee (EmployeeCode) of every day and I need to have a sum on sript level.

part of my code:

LOAD Date,
Month(Timestamp(Date,'YYYY-MM-DD hh:mm:ss ')) as Month,
EmployeeCode,
HoursWorked;
SQL SELECT Date,
EmployeeCode,
HoursWorked

FROM Employer2003.dbo."Vw_WorkedHoursAttendance"
where Date>= '2008-01-01';

thank you very much for answers

Ales

[#2 · Accepted solution]

Hi Ales,

You can use GROUP BY just like in SQL. Here is how your code might look like:

Detail:
LOAD
 *,
 Month( %dateTime ) AS [Month];

LOAD
 *,
 timestamp( timestamp#( Date, 'M/D/YYYY hh:mm:ss tt' ) ) AS %dateTime;

SQL SELECT
 Date,
 EmployeeCode,
 HoursWorked
FROM
 Employer2003.dbo."Vw_WorkedHoursAttendance"
WHERE
 Date>= '2008-01-01';

HoursByMonth:
LOAD
 [Month],
 EmployeeCode,
 sum( HoursWorked )
RESIDENT
 Detail
GROUP BY
 [Month],
 EmployeeCode;

You might, however, want to group by both month and year so you can load more than one year's worth of data.

Hope this helps,

Hellmar

[#3]

If i'm correct, there is no need to sum at script level for your purpose.
You can load the data as it is from the table, and during presenting the data in
front end, you can get it summed on a monthy basis.

Whenever date is coming into play, its always better to create a calendar and relate
it with the table in which date is present.

Refer the attached qvw file, which i think serves your purpose.

[#4]

Thank you Hellmar, it helped me 🙂

A.