Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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))


nameServiceDateSemainemonthdayHours Arrival
agent 5INFORMATIQUE04/02/20146FebruaryTuesday 07:56:08
agent 7INFORMATIQUE04/02/20146FebruaryTuesday 07:52:20
agent 1INFORMATIQUE03/02/20146FebruaryMonday08:37:58
agent 1INFORMATIQUE04/02/20146FebruaryTuesday 08:15:14
agent 1INFORMATIQUE05/02/20146FebruaryWednesday08:38:14
agent 1INFORMATIQUE06/02/20146FebruaryThursday 08:20:36
agent 4INFORMATIQUE03/02/20146FebruaryMonday07:45:54
agent 4INFORMATIQUE07/02/20146FebruaryFriday07:50:46
agent 6INFORMATIQUE05/02/20146FebruaryWednesday13:23:36
agent 6INFORMATIQUE07/02/20146FebruaryFriday15:38:20
agent 2INFORMATIQUE03/02/20146FebruaryMonday07:49:18
agent 2INFORMATIQUE04/02/20146FebruaryTuesday 07:49:12
agent 2INFORMATIQUE05/02/20146FebruaryWednesday08:03:18
agent 2INFORMATIQUE06/02/20146FebruaryThursday 08:08:40
agent 2INFORMATIQUE07/02/20146FebruaryFriday07:58:28
agent 3INFORMATIQUE04/02/20146FebruaryTuesday 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

Tags (4)
1 Solution

Accepted Solutions
MVP
MVP

Re: term sum or count

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
MVP
MVP

Re: term sum or count

RESULT

2014-03-19 21_38_51-QlikView x64 - [C__Users_mgrossi_Downloads_111209.qvw_].png

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;

Not applicable

Re: term sum or count

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

MVP
MVP

Re: term sum or count

I supposed your source data was in base.xls

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

Not applicable

Re: term sum or count

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

MVP
MVP

Re: Re: term sum or count

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;

Not applicable

Re: Re: term sum or count

I do not understand, I still have the same error

error.png

MVP
MVP

Re: term sum or count

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?

Not applicable

Re: term sum or count

no error

Not applicable

Re: term sum or count

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


result.png

Community Browser