Forum Discussion
JamesFR06
Resolver IV
Resolver IVHelp on holdays calculation
Hi,
This is my table :
| Matricule | Beg_Date | Ending_Date | Reason | Type Abs | Dab_Abs | End_Abs | Duration | Dur_rk |
| S21095 | 08/02/2022 | 08/02/2022 | FORMATION | Consentie | 08/02/2022 | 10/02/2022 | 3 | Less 5 days |
| S21095 | 09/02/2022 | 09/02/2022 | FORMATION | Consentie | 08/02/2022 | 10/02/2022 | 3 | Less 5 days |
| S21095 | 10/02/2022 | 10/02/2022 | FORMATION | Consentie | 08/02/2022 | 10/02/2022 | 3 | Less 5 days |
| S21095 | 05/04/2022 | 05/04/2022 | ABSENCE NON PAYEE | Subie | 05/04/2022 | 05/04/2022 | 1 | Less 5 days |
| S21095 | 27/05/2022 | 27/05/2022 | CONGES | Consentie | 27/05/2022 | 27/05/2022 | 1 | Less 5 days |
| S21095 | 06/06/2022 | 06/06/2022 | CONGES | Consentie | 06/06/2022 | 06/06/2022 | 1 | Less 5 days |
My problem is due to the HR system sometimes for a Holiday period I can have many rows. I cannot do it on query !!
I would like to add these 4 columns in my table. SO when there is only 1 line it is ok, more I do not succeed to achieve this.
The code should read each row and look at the difference between the previous End Abs row value and the beg value of the active row. And if difference is 1 it is the same period etc.....
ANy help would be very appreciate !!
JamesFR06 So, perhaps something like this, although this would not be 100% foolproof:
Dab_Abs Column = VAR __Matricule = [Matricule] VAR __Reason = [Reason] VAR __TypeAbs = [Type Abs] VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs) VAR __Resut = MINX( __Table, [Beg_Date] ) RETURN __Result End_Abs Column = VAR __Matricule = [Matricule] VAR __Reason = [Reason] VAR __TypeAbs = [Type Abs] VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs) VAR __Resut = MAXX( __Table, [Ending_Date] ) RETURN __Result Duration = ([End_Abs Column] - [Dab_Abs Column]) * 1. Dur_rk = IF( [Duration] < 5, "Less 5 days", "5 day or more" )
1 Reply
- Greg_Deckler
Community Champion
JamesFR06 So, perhaps something like this, although this would not be 100% foolproof:
Dab_Abs Column = VAR __Matricule = [Matricule] VAR __Reason = [Reason] VAR __TypeAbs = [Type Abs] VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs) VAR __Resut = MINX( __Table, [Beg_Date] ) RETURN __Result End_Abs Column = VAR __Matricule = [Matricule] VAR __Reason = [Reason] VAR __TypeAbs = [Type Abs] VAR __Table = FILTER( 'Table', [Matricule] = __Matricule && [Reason] = __Reason && [Type Abs] = __TypeAbs) VAR __Resut = MAXX( __Table, [Ending_Date] ) RETURN __Result Duration = ([End_Abs Column] - [Dab_Abs Column]) * 1. Dur_rk = IF( [Duration] < 5, "Less 5 days", "5 day or more" )