13 Replies Latest reply: Mar 20, 2014 10:24 AM by Massimo Grossi RSS

    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

        • Re: term sum or count
          Massimo Grossi

          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;

            • 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

                • Re: term sum or count
                  Massimo Grossi

                  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

                      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
                          Massimo Grossi

                          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;