Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead 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))


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

1 Solution

Accepted Solutions
Highlighted
MVP
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)

View solution in original post

13 Replies
Highlighted
MVP
MVP

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;

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

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

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

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;

Highlighted
Not applicable

I do not understand, I still have the same error

error.png

Highlighted
MVP
MVP

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?

Highlighted
Not applicable

no error

Highlighted
Not applicable

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


result.png