topic Re: term sum or count in QlikView

term sum or count

Wed, 19 Mar 2014 10:37:46 GMT

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

Re: term sum or count

maxgro — Wed, 19 Mar 2014 22:26:55 GMT

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;

Re: term sum or count

Thu, 20 Mar 2014 10:18:50 GMT

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

Re: term sum or count

maxgro — Thu, 20 Mar 2014 10:46:39 GMT

I supposed your source data was in base.xls

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

Re: term sum or count

Thu, 20 Mar 2014 11:04:30 GMT

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

Re: Re: term sum or count

maxgro — Thu, 20 Mar 2014 12:20:31 GMT

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;

Re: Re: term sum or count

Thu, 20 Mar 2014 13:18:39 GMT

I do not understand, I still have the same error

Re: term sum or count

maxgro — Thu, 20 Mar 2014 13:23:18 GMT

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 *

exit script;

is there a table called Source in Table Viewer?

Re: term sum or count

Thu, 20 Mar 2014 13:32:40 GMT

no error

Re: term sum or count

Thu, 20 Mar 2014 13:39:59 GMT

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

Re: term sum or count

Thu, 20 Mar 2014 13:55:22 GMT

if I create a simple table with user id how I will use the term count
count ({$ <FLAGLATE = {1}} FLAGMINHOURS)

Re: term sum or count

maxgro — Thu, 20 Mar 2014 13:58:30 GMT

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)

Re: term sum or count

Thu, 20 Mar 2014 14:15:22 GMT

thank you it works well

Re: term sum or count

maxgro — Thu, 20 Mar 2014 14:24:55 GMT

Please close this discussion by giving Correct and Helpful answers to the posts which are useful for you. It helps others in finding answers for similar scenarios.