topic Re: Count If in QlikView

Count If

Sun, 16 Nov 2014 19:40:44 GMT

I need to create a stacked bar chart using timestamp data. It is used to show time worked per day by employee. My data (Fact) table has the employee name, punch in (Start) and punch out (End). I need help creating a field that counts the number of times a person punched in on a given day. For example, an employee could have have 3 punch times in one day, that may not necessarily be in chronological order.

How can I assign that row of data a punch count as displayed below for John Doe? The criteria would be based on the Empl name and date.

Empl	Date	Start	End	Type	Punch_Count
John Doe	11/16/14	11/16/14 5:00 AM	11/16/14 7:24 AM	Shift	1
Jane Doe	11/16/14	11/16/14 5:03 AM	11/16/14 11:03 AM	Shift	1
John Doe	11/16/14	11/16/14 8:07 AM	11/16/14 12:55 PM	Shift	3
John Doe	11/16/14	11/16/14 7:24 AM	11/16/14 8:07 AM	Break	2
Jane Doe	11/16/14	11/16/14 11:03 AM	11/16/14 12:15 PM	Break	2

Thanks for all of your help in advance.

Re: Count If

MK_QSL — Sun, 16 Nov 2014 19:51:58 GMT

Temp:

Load

Empl,

Date(Date#(Date,'MM/DD/YY')) as Date,

TimeStamp(Timestamp#(Start,'MM/DD/YY h:mm TT')) as Start,

TimeStamp(Timestamp#(End,'MM/DD/YY h:mm TT')) as End

Inline

[

Empl, Date, Start, End, Type, Punch_Count

John Doe, 11/16/14, 11/16/14 5:00 AM, 11/16/14 7:24 AM, Shift, 1

Jane Doe, 11/16/14, 11/16/14 5:03 AM, 11/16/14 11:03 AM, Shift, 1

John Doe, 11/16/14, 11/16/14 8:07 AM, 11/16/14 12:55 PM, Shift, 3

John Doe, 11/16/14, 11/16/14 7:24 AM, 11/16/14 8:07 AM, Break, 2

Jane Doe, 11/16/14, 11/16/14 11:03 AM, 11/16/14 12:15 PM, Break, 2

];

Final:

Load

If(Empl = Previous(Empl) and Date = Previous(Date),RangeSum(Peek('Punch_Count'),1),1) as Punch_Count

Resident Temp

Order By Empl,Date, Start;

Drop Table Temp;

Re: Count If

MarcoWedel — Sun, 16 Nov 2014 20:06:32 GMT

Left Join (yourtable)

Load *,

Autonumber(Start, Empl&'/'&Date)

Resident yourtable

Order by Start;

Hope this helps

regards

Marco

Re: Count If

Sun, 16 Nov 2014 21:10:28 GMT

Thanks a lot!

It appears the previous function slows down the load significantly, but it does exactly what I need it to do so thank you.