Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
add a tablebox using Table and verify if the records you wants are flagged
if yes build the chart using the expression in my attachment (modified because of different source data);
dimension idusager
expression count({$ <FLAGLATE={1},FLAGMINHOURS={1}>} DISTINCT date)
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;
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
I supposed your source data was in base.xls
I put base.xls in the same folder as 111147.qvw
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());
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;
I do not understand, I still have the same error
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?
no error
ok ca no longer generates an error, but it shows me