topic Re: Enter time track in QlikView

Enter time track

master_student — Thu, 29 Sep 2016 11:29:16 GMT

Hello Guys,

I need to develop a table that track the time when employees enter to the work building (morning/afternoon) after work hour. This dashboards is destined to directors to follow their collaborators.

Heres the rule for that table :

if the morning time between 8 and 9 => display the first time after 8 ; if not display the first time after 9

if the evening time between 14 and 15 => => display the first time after 14 ; if not display the first time after 15

The MIN_DATE_DEBhas this format (date) ;

the output result would be somethin like that :

Any help please..

Re: Enter time track

Anil_Babu_Samineni — Thu, 29 Sep 2016 11:49:04 GMT

I am not sure I fully understand your concern. Would you provide brief expansion along with the output

Re: Enter time track

master_student — Thu, 29 Sep 2016 12:40:01 GMT

i post my expected output result on the first message

Re: Enter time track

Anonymous — Thu, 29 Sep 2016 13:13:35 GMT

Hi Wiem,

Load min value of pointage apres Midi and min of pointage Matin, for each matricule, nom and jour. You will have the min hour that each employee have entered. Then, for the indicator, use this:

=If(Hour(MinPointageMatin) < 9 and Hour(MinPointageAPresMidi) < 15,'qmem://<bundled>/BuiltIn/smiley1_g.png',

If((Hour(MinPointageMatin) >= 9 and Hour(MinPointageAPresMidi) < 15) or (Hour(MinPointageMatin) < 9 and Hour(MinPointageAPresMidi) >= 15),'qmem://<bundled>/BuiltIn/smiley2_y.png',

'qmem://<bundled>/BuiltIn/smiley3_r.png'))

Regards!!!

Re: Enter time track

master_student — Thu, 29 Sep 2016 13:27:59 GMT

I need to change you condition like that

=If(Hour(MinPointageMatin) >8 and Hour(MinPointageAPresMidi) < 9 => display the first time after 8

the min entred hour in my case is the first time between 8 and 9, any idea please

Re: Enter time track

Anonymous — Thu, 29 Sep 2016 13:36:21 GMT

Hi Weim, can the employee enter at 7:45?

Re: Enter time track

vishsaggi — Thu, 29 Sep 2016 13:45:19 GMT

I am not sure the exact expression but you can try using in your

If(Hour(MinPointageMatin) >8 and Hour(MinPointageAPresMidi) < 9, Min(Timefield) or FirstSortedValue() ) in your expression ? Can you post the complete expression if possible. ?

Re: Enter time track

master_student — Thu, 29 Sep 2016 13:46:42 GMT

Yes he can do the pointage (enter ) at that time and his pointage will be registred in the database but I need to display only the pointage time between 8 and 9 THE first one 5 (pointage matin)

In my case, my database contains all times where the employees enter to the building

Mat date_deb

1 29/09/2016 08:20:30

1 29/09/2016 10:20:00

1 29/09/2016 14:20 : 00

how to do that in the script??

Re: Enter time track

master_student — Thu, 29 Sep 2016 13:54:27 GMT

I did like that but didn't work :

If(Hour(DATE_DEB) >8 and Hour(DATE_DEB) < 9, Min(Time(Frac(DATE_DEB))) )

the date_deb field has this format :

Re: Enter time track

Anonymous — Thu, 29 Sep 2016 14:11:42 GMT

Then, you need only to load Min dates of each employee and jour:

Load

Employee,

Jour,

Min(PointageMatin) as MinPointageMatin,

Min(PointageAPresMidi) as MinPointageAPresMidi

RESIDENT YourTable

WHERE Hour(PointageMatin) >= 8

GROUP BY Employee, Jour;

With this you load all values where employees have entered after 8. Then, with the min values, you have the min matin and min apres midi hours that employees have entered.

Regards!!

Re: Enter time track

vishsaggi — Thu, 29 Sep 2016 14:34:24 GMT

Does this formula -> Time(Frac(DATE_DEB)) displays the time only?

Re: Enter time track

Thu, 29 Sep 2016 14:46:12 GMT

Hi,

Can you please try below.

If(Hour(DATE_DEB) >8 and Hour(DATE_DEB) < 9, mid(DATE_DEB, Index(DATE_DEB,' ')+1) ) and let me know if it works

Thanks,
Sreeman

Re: Enter time track

master_student — Thu, 29 Sep 2016 14:47:28 GMT

yes

Re: Enter time track

vishsaggi — Thu, 29 Sep 2016 14:49:27 GMT

would it be possible to share a sample app to work on it. ?

Re: Enter time track

master_student — Thu, 29 Sep 2016 15:11:58 GMT

this script works

if(

// Compare if the time from DATE_DEB

time(DATE_DEB,'DD/MM/YYYY hh:mm:ss')

// is greater then

// 13:00:00

time(date(floor(DATE_DEB) ,'DD/MM/YYYY') + time(MakeTime(8),'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss') ,

// if so, DATE_DEB

time(DATE_DEB),

// If not, null() value

null()

) as DATEE,

but whene I did that between 8 and 9 it didn't

if(

// Compare if the time from DATE_DEB

time(DATE_DEB,'DD/MM/YYYY hh:mm:ss')

// is greater then

// 08:00:00

time(date(floor(DATE_DEB) ,'DD/MM/YYYY') + time(MakeTime(8),'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

and

// Compare if the time from DATE_DEB

time(DATE_DEB,'DD/MM/YYYY hh:mm:ss')

// is greater then

time(date(floor(DATE_DEB) ,'DD/MM/YYYY') + time(MakeTime(9),'hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

// if so, DATE_DEB

time(DATE_DEB),

// If not, null() value

null()

) as DATEE,

Re: Enter time track

master_student — Thu, 29 Sep 2016 15:12:10 GMT

Re: Enter time track

vishsaggi — Thu, 29 Sep 2016 15:23:47 GMT

Can you try this?

time(Date#(DATE_DEB,'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss') -- where ever us used this ?

Re: Enter time track

Thu, 29 Sep 2016 18:07:58 GMT

Thanks for checking. Can you please try the below. I tried it and it is working fine.

if(replace(mid(a, Index(a,' ')+1),':','')>130000 and replace(mid(a, Index(a,' ')+1),':','')<140000,mid(a, Index(a,' ')+1) ,null)

Here, 'a' means DATA_DEB