# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Not applicable

## term sum or count

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

1 Solution

Accepted Solutions
Highlighted
MVP

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)

13 Replies
Highlighted
MVP

RESULT

SCRIPT

Directory;

Source:

DATE,

MONTH,

HOURS,

ZONE,

SERVICE

FROM

base.xls

(biff, embedded labels, table is Sheet1\$)

;

Table:

*,

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;

Highlighted
Not applicable

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

Highlighted
MVP

I supposed your source data was in base.xls

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

Highlighted
Not applicable

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);

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 vDateToday = Num(Today());

TempCalendar:

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

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

AUTOGENERATE 1

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

MasterCalendar:

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 vDateToday = Num(Today());

Highlighted
MVP

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:

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:

*,

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;

Highlighted
Not applicable

I do not understand, I still have the same error

Highlighted
MVP

try to stop execution after first load

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

Source:

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?

Highlighted
Not applicable

no error

Highlighted
Not applicable

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