term sum or count

[#1]

Hello to all

I am a beginner in QlikView, I try to make a qw report is based on a path of personal drive (time of entry, time out, areas, etc..).

I am looking for a formula (expression) that will allow me to count the number of times an individual is late from 7am 45

I can retrieve all day from late this expression:

=if((CalendarWeekDay = 'Monday' or  CalendarWeekDay =' Tuesday ' or CalendarWeekDay =' Wednesday ' or  CalendarWeekDay =' Thursday ' or CalendarWeekDay =' Friday')) and min((hours),1)>=maketime(7,45),min((hours),1))

name	Service	Date	Semaine	month	day	Hours Arrival
agent 5	INFORMATIQUE	04/02/2014	6	February	Tuesday	07:56:08
agent 7	INFORMATIQUE	04/02/2014	6	February	Tuesday	07:52:20
agent 1	INFORMATIQUE	03/02/2014	6	February	Monday	08:37:58
agent 1	INFORMATIQUE	04/02/2014	6	February	Tuesday	08:15:14
agent 1	INFORMATIQUE	05/02/2014	6	February	Wednesday	08:38:14
agent 1	INFORMATIQUE	06/02/2014	6	February	Thursday	08:20:36
agent 4	INFORMATIQUE	03/02/2014	6	February	Monday	07:45:54
agent 4	INFORMATIQUE	07/02/2014	6	February	Friday	07:50:46
agent 6	INFORMATIQUE	05/02/2014	6	February	Wednesday	13:23:36
agent 6	INFORMATIQUE	07/02/2014	6	February	Friday	15:38:20
agent 2	INFORMATIQUE	03/02/2014	6	February	Monday	07:49:18
agent 2	INFORMATIQUE	04/02/2014	6	February	Tuesday	07:49:12
agent 2	INFORMATIQUE	05/02/2014	6	February	Wednesday	08:03:18
agent 2	INFORMATIQUE	06/02/2014	6	February	Thursday	08:08:40
agent 2	INFORMATIQUE	07/02/2014	6	February	Friday	07:58:28
agent 3	INFORMATIQUE	04/02/2014	6	February	Tuesday	08:00:26

Now my problem is how can we have the sum of all minimum an agent or counting to get a result like that.

agent 1 = 4

agent 2 = 5

agent 3 = 1

agent 4 = 2

agent 5 = 1

agent 6 = 2

agent 7 = 1

[maxgro]

RESULT

SCRIPT

Directory;

Source:

LOAD NAME,

    DATE,

    MONTH,

    HOURS,

    ZONE,

    SERVICE

FROM

base.xls

(biff, embedded labels, table is Sheet1$)

;

Table:

NoConcatenate load

  *,

if(match(num(WeekDay(DATE)),0,1,2,3,4) and HOURS>=maketime(7,45),1,0) as FLAGLATE,

if(NAME<>peek(NAME) or DATE<>Peek(DATE),1,0) as FLAGMINHOURS

Resident Source

order by NAME, DATE, HOURS;

DROP Table Source;

[#3]

Hello and thank you for the answer

but the problem is that I can not have the same result you I me it shows me an error loading table not found

[maxgro]

I supposed your source data was in base.xls

I put base.xls in the same folder as 111147.qvw

[#5]

my data is not in the xls file in a database but here is the script my script:

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

LOAD
    idusager,
    Date(Floor("Date de Passage")) as date,
    Time(Frac("Date de Passage")) as heure,
    nom,
    prenom,
    Evenement,
    idpropriete,
    matricule,
    libelle;
   
SQL SELECT *
FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

/**************calendar**************************/

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

  $(vDateMin) + RowNo() - 1 AS DateNumber,

  Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

    TempDate AS date,

    Day(TempDate) AS CalendarDay,

    WeekDay(TempDate) AS CalendarWeekDay,

    Week(TempDate) AS CalendarWeek,

    Month(TempDate) AS Mois,

    Year(TempDate) AS Année,

    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

[maxgro]

I changed first part of your script, before the calendar

using my logic and your tables/fields

I think you want to know if the first heure by idusager and date is before or after 7:45

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

Source:

LOAD

    idusager,

    Date(Floor("Date de Passage")) as date,

    Time(Frac("Date de Passage")) as heure,

    nom,

    prenom,

    Evenement,

    idpropriete,

    matricule,

    libelle;

SQL SELECT *

FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

Table:

NoConcatenate load

  *,

if(match(num(WeekDay(date)),0,1,2,3,4) and heure>=maketime(7,45),1,0) as FLAGLATE,

if(idusager<>peek(idusager) or date<>Peek(date),1,0) as FLAGMINHOURS          // flag first by idusager date

Resident Source

order by idusager, date, heure;

DROP Table Source;

[#7]

I do not understand, I still have the same error

[maxgro]

try to stop execution after first load

ODBC CONNECT TO MIS (XUserId is aPDDeQRMNLacWYB, XPassword is TZAfHYFMTbcOXXJOEDKB);

Source:

LOAD

    idusager,

    Date(Floor("Date de Passage")) as date,

    Time(Frac("Date de Passage")) as heure,

    nom,

    prenom,

    Evenement,

    idpropriete,

    matricule,

    libelle;

SQL SELECT *

FROM Alizes.dbo."View_SATF_Historique Passage"  WHERE (idpropriete='3')  AND ( ( nom NOT LIKE ('%VISITEUR%') AND (nom NOT LIKE ('%BADGE%')) AND (nom NOT LIKE ( '%SURETE%'))  AND (nom NOT LIKE ( '%PRESTATAIRE%'))  )  )  ;

exit script;

is there  a table called Source in Table Viewer?

[#9]

no error

[#10]

ok ca no longer generates an error, but it shows me