Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Mr_H_Huang
Contributor III
Contributor III
‎2023-10-20 08:21 AM

Solved: A method to repeate the Autonumber or RowNo() for a rolling period of date/time using MOD

I have been reading the posts in the community for seeking helps for years. Today I would like to give back to the community to share a method to repeate the Autonumber or RowNo() for a rolling period of date using MOD. As I couldn't find an answer related to my specific requirement. I hope it helps. 

 

In short you may jump to read point 4 and point 7.  In Qlik we rarely use the sytax MOD. I very occasionally come across the use of MOD when I was learning Python. Then I test the use of MOD in Excel with the exported Qlik data set for quicker testing cycle becuase my brain has stuck in how it works repeatly. So it means the same syntax MOD can be used and perform the same in both Python, Qlik, and Excel. Maybe this is a good way to do testing quickly to verify the prototype.  

Mod() is a modulo function that returns the non-negative remainder of an integer division. The first argument is the dividend, the second argument is the divisor, Both arguments must be integer values.

Syntax:  

Mod(integer_number1, integer_number2)

Return data type: integer

Limitations:  

integer_number2 must be greater than 0.

Examples and results:  

Mod( 7,2 ): returns 1

Mod( 7.5,2 ): returns NULL

Mod( 9,3 ): returns 0

Mod( -4,3 ): returns 2

Mod( 4,-3 ): returns NULL

Mod( -4,-3 ): returns NULL

 

//4, the most most most important part here below is to create a field MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS day_number_MOD.
This  day_number_MOD creates a single Autonumber or RowNo(),  the combination of day_number_MOD and ROSTR_CODE can refer to the roster's working hours in each cycle length date using MAPPING later. 
 

//7, finally we generate the full list of working hours for every date in the Master calendar for each ROSTR_CODE, the full list of working hours follows the pattern of the roster cycle length the same as the original start date. We get the MOD here similar to what we have done in the above mapping. Be awared the MOD here is the full roster cycle length repeated MOD for the whole Master calendar. 

The MOD we creates here is a repeate Autonumber or RowNo(),  the combination of MOD and ROSTR_CODE can refer to the roster's working hours in each cycle length date using APPLYMAP. 

MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS MOD,

APPLYMAP('ROSTR_CODE_DAY_NUMBER_TO_HOURS', ROSTR_CODE &'|'& MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) ,'') As working_hours_full_length,

 

 


The user requirement is show a rolling roster working hours/date/day for the employee. Different employee may have different working roster patterns. Even a same employee may have different working roster patterns throught out different period of their employment. 

ROSTR_CODE repensents each working pattern, an employee may has one or more ROSTR_CODE during their employment. 
RWP_CYCL_LEN repensents the days of roster cycle length of each ROSTR_CODE.

RSRWP.RWP_START_DT repensents the start date of when ROSTR_CODE 'sroster cycle length begun. 

Each ROSTR_CODE has got a fixed 30 columns of starting date (the important date to refer) and its following date, a fixed 30 columns of week days (we don't really care the week days, as it can be easily available in any Master table), and a fixed 30 columns of working hours (the target field) during this period of date. Total 93 columns.

If the RWP_CYCL_LEN has more than 30 days like 56 days, it has got an additional row 2 to show the additonal 30 columns of each elements mentioned above, as total 93 columns. In row 2 the RSRWP.RWP_START_DT still shows the real ROSTR_CODE start date the same as row 1' start date for a same ROSTR_CODE . 

Original table:

RSRWP.ROSTR_CODE RSRWP.RWP_CYCL_LEN RSRWP.RWP_START_DT RSRWP.RWP_DATE01 RSRWP.RWP_DATE02 RSRWP.RWP_DATE03 RSRWP.RWP_DATE04 RSRWP.RWP_DATE05 RSRWP.RWP_DATE06 RSRWP.RWP_DATE07 RSRWP.RWP_DATE08 RSRWP.RWP_DATE09 RSRWP.RWP_DATE10 RSRWP.RWP_DATE11 RSRWP.RWP_DATE12 RSRWP.RWP_DATE13 RSRWP.RWP_DATE14 RSRWP.RWP_DATE15 RSRWP.RWP_DATE16 RSRWP.RWP_DATE17 RSRWP.RWP_DATE18 RSRWP.RWP_DATE19 RSRWP.RWP_DATE20 RSRWP.RWP_DATE21 RSRWP.RWP_DATE22 RSRWP.RWP_DATE23 RSRWP.RWP_DATE24 RSRWP.RWP_DATE25 RSRWP.RWP_DATE26 RSRWP.RWP_DATE27 RSRWP.RWP_DATE28 RSRWP.RWP_DATE29 RSRWP.RWP_DATE30 RSRWP.RWP_DAY_WK01 RSRWP.RWP_DAY_WK02 RSRWP.RWP_DAY_WK03 RSRWP.RWP_DAY_WK04 RSRWP.RWP_DAY_WK05 RSRWP.RWP_DAY_WK06 RSRWP.RWP_DAY_WK07 RSRWP.RWP_DAY_WK08 RSRWP.RWP_DAY_WK09 RSRWP.RWP_DAY_WK10 RSRWP.RWP_DAY_WK11 RSRWP.RWP_DAY_WK12 RSRWP.RWP_DAY_WK13 RSRWP.RWP_DAY_WK14 RSRWP.RWP_DAY_WK15 RSRWP.RWP_DAY_WK16 RSRWP.RWP_DAY_WK17 RSRWP.RWP_DAY_WK18 RSRWP.RWP_DAY_WK19 RSRWP.RWP_DAY_WK20 RSRWP.RWP_DAY_WK21 RSRWP.RWP_DAY_WK22 RSRWP.RWP_DAY_WK23 RSRWP.RWP_DAY_WK24 RSRWP.RWP_DAY_WK25 RSRWP.RWP_DAY_WK26 RSRWP.RWP_DAY_WK27 RSRWP.RWP_DAY_WK28 RSRWP.RWP_DAY_WK29 RSRWP.RWP_DAY_WK30 RSRWP.RWP_HOURS01 RSRWP.RWP_HOURS02 RSRWP.RWP_HOURS03 RSRWP.RWP_HOURS04 RSRWP.RWP_HOURS05 RSRWP.RWP_HOURS06 RSRWP.RWP_HOURS07 RSRWP.RWP_HOURS08 RSRWP.RWP_HOURS09 RSRWP.RWP_HOURS10 RSRWP.RWP_HOURS11 RSRWP.RWP_HOURS12 RSRWP.RWP_HOURS13 RSRWP.RWP_HOURS14 RSRWP.RWP_HOURS15 RSRWP.RWP_HOURS16 RSRWP.RWP_HOURS17 RSRWP.RWP_HOURS18 RSRWP.RWP_HOURS19 RSRWP.RWP_HOURS20 RSRWP.RWP_HOURS21 RSRWP.RWP_HOURS22 RSRWP.RWP_HOURS23 RSRWP.RWP_HOURS24 RSRWP.RWP_HOURS25 RSRWP.RWP_HOURS26 RSRWP.RWP_HOURS27 RSRWP.RWP_HOURS28 RSRWP.RWP_HOURS29 RSRWP.RWP_HOURS30
CK0014 7 2022-03-27 2022-03-27 2022-03-28 2022-03-29 2022-03-30 2022-03-31 2022-04-01 2022-04-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 7 1 2 3 4 5 6                                               0 3 7 3 3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BF0031 7 2022-02-13 2022-02-13 2022-02-14 2022-02-15 2022-02-16 2022-02-17 2022-02-18 2022-02-19 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 7 1 2 3 4 5 6                                               0 7.5 7.5 0 7.5 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BF0012 14 2022-01-23 2022-01-23 2022-01-24 2022-01-25 2022-01-26 2022-01-27 2022-01-28 2022-01-29 2022-01-30 2022-01-31 2022-02-01 2022-02-02 2022-02-03 2022-02-04 2022-02-05 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 7 1 2 3 4 5 6 7 1 2 3 4 5 6                                 0 0 0 11.33 11.33 11.33 0 0 11.33 11.33 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BF0017 14 2022-01-09 2022-01-09 2022-01-10 2022-01-11 2022-01-12 2022-01-13 2022-01-14 2022-01-15 2022-01-16 2022-01-17 2022-01-18 2022-01-19 2022-01-20 2022-01-21 2022-01-22 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 0001-01-02 7 1 2 3 4 5 6 7 1 2 3 4 5 6                                 0 11.33 0 0 0 0 11.33 0 0 0 11.33 11.33 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
BF0018 56 2021-09-19 2021-10-19 2021-10-20 2021-10-21 2021-10-22 2021-10-23 2021-10-24 2021-10-25 2021-10-26 2021-10-27 2021-10-28 2021-10-29 2021-10-30 2021-10-31 2021-11-01 2021-11-02 2021-11-03 2021-11-04 2021-11-05 2021-11-06 2021-11-07 2021-11-08 2021-11-09 2021-11-10 2021-11-11 2021-11-12 2021-11-13 0001-01-02 0001-01-02 0001-01-02 0001-01-02 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6         0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 0 0 0
BF0019 56 2021-09-19 2021-10-19 2021-10-20 2021-10-21 2021-10-22 2021-10-23 2021-10-24 2021-10-25 2021-10-26 2021-10-27 2021-10-28 2021-10-29 2021-10-30 2021-10-31 2021-11-01 2021-11-02 2021-11-03 2021-11-04 2021-11-05 2021-11-06 2021-11-07 2021-11-08 2021-11-09 2021-11-10 2021-11-11 2021-11-12 2021-11-13 0001-01-02 0001-01-02 0001-01-02 0001-01-02 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6         0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 0 0 0
BF0018 56 2021-09-19 2021-09-19 2021-09-20 2021-09-21 2021-09-22 2021-09-23 2021-09-24 2021-09-25 2021-09-26 2021-09-27 2021-09-28 2021-09-29 2021-09-30 2021-10-01 2021-10-02 2021-10-03 2021-10-04 2021-10-05 2021-10-06 2021-10-07 2021-10-08 2021-10-09 2021-10-10 2021-10-11 2021-10-12 2021-10-13 2021-10-14 2021-10-15 2021-10-16 2021-10-17 2021-10-18 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0
BF0019 56 2021-09-19 2021-09-19 2021-09-20 2021-09-21 2021-09-22 2021-09-23 2021-09-24 2021-09-25 2021-09-26 2021-09-27 2021-09-28 2021-09-29 2021-09-30 2021-10-01 2021-10-02 2021-10-03 2021-10-04 2021-10-05 2021-10-06 2021-10-07 2021-10-08 2021-10-09 2021-10-10 2021-10-11 2021-10-12 2021-10-13 2021-10-14 2021-10-15 2021-10-16 2021-10-17 2021-10-18 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 2 3 4 5 6 7 1 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0 0 0 0 11 11 11 11 0

 

//1, I create a field GroupRow to distinguish and load them one by one before cross join


TEST_RSRWP_TEMP:
LOAD
ROSTR_CODE,
RWP_CYCL_LEN,
RWP_START_DT,
IF(ROSTR_CODE <> Previous(ROSTR_CODE), 1, Peek('GroupRow')+1) AS GroupRow, //If there are two rows for each ROSTER_CODE before cross join, we need to distinguish and load them one by one

RWP_DATE01,
RWP_DAY_WK01,
RWP_HOURS01,
RWP_DATE02,
RWP_DAY_WK02,
RWP_HOURS02,
RWP_DATE03,
RWP_DAY_WK03,
RWP_HOURS03,
RWP_DATE04,
RWP_DAY_WK04,
RWP_HOURS04,
RWP_DATE05,
RWP_DAY_WK05,
RWP_HOURS05,
RWP_DATE06,
RWP_DAY_WK06,
RWP_HOURS06,
RWP_DATE07,
RWP_DAY_WK07,
RWP_HOURS07,
RWP_DATE08,
RWP_DAY_WK08,
RWP_HOURS08,
RWP_DATE09,
RWP_DAY_WK09,
RWP_HOURS09,
RWP_DATE10,
RWP_DAY_WK10,
RWP_HOURS10,
RWP_DATE11,
RWP_DAY_WK11,
RWP_HOURS11,
RWP_DATE12,
RWP_DAY_WK12,
RWP_HOURS12,
RWP_DATE13,
RWP_DAY_WK13,
RWP_HOURS13,
RWP_DATE14,
RWP_DAY_WK14,
RWP_HOURS14,
RWP_DATE15,
RWP_DAY_WK15,
RWP_HOURS15,
RWP_DATE16,
RWP_DAY_WK16,
RWP_HOURS16,
RWP_DATE17,
RWP_DAY_WK17,
RWP_HOURS17,
RWP_DATE18,
RWP_DAY_WK18,
RWP_HOURS18,
RWP_DATE19,
RWP_DAY_WK19,
RWP_HOURS19,
RWP_DATE20,
RWP_DAY_WK20,
RWP_HOURS20,
RWP_DATE21,
RWP_DAY_WK21,
RWP_HOURS21,
RWP_DATE22,
RWP_DAY_WK22,
RWP_HOURS22,
RWP_DATE23,
RWP_DAY_WK23,
RWP_HOURS23,
RWP_DATE24,
RWP_DAY_WK24,
RWP_HOURS24,
RWP_DATE25,
RWP_DAY_WK25,
RWP_HOURS25,
RWP_DATE26,
RWP_DAY_WK26,
RWP_HOURS26,
RWP_DATE27,
RWP_DAY_WK27,
RWP_HOURS27,
RWP_DATE28,
RWP_DAY_WK28,
RWP_HOURS28,
RWP_DATE29,
RWP_DAY_WK29,
RWP_HOURS29,
RWP_DATE30,
RWP_DAY_WK30,
RWP_HOURS30
FROM RSRWP...

ORDER BY
ROSTR_CODE,
RWP_CYCL_LEN;

 

//2, I use cross table to turn the rows into columns for a more readable/ handlable result, with FOR NEXT to load every GroupRow. Please advise if you have a better idea to automatic/variablise the GroupRow "FOR a = 1 to 2" rather than hardcoded like me. 

FOR a = 1 to 2 //If there are two or more rows for each ROSTER_CODE before cross join, we need to change here from 2 to larger number
working_day_temp:
CrossTable(working_week_number, working_week_day, 4)
LOAD ROSTR_CODE, 
     RWP_CYCL_LEN,
     RWP_START_DT, 
GroupRow,
     RWP_DAY_WK01, 
     RWP_DAY_WK02, 
     RWP_DAY_WK03, 
     RWP_DAY_WK04, 
     RWP_DAY_WK05, 
     RWP_DAY_WK06, 
     RWP_DAY_WK07, 
     RWP_DAY_WK08, 
     RWP_DAY_WK09, 
     RWP_DAY_WK10, 
     RWP_DAY_WK11, 
     RWP_DAY_WK12, 
     RWP_DAY_WK13, 
     RWP_DAY_WK14, 
     RWP_DAY_WK15, 
     RWP_DAY_WK16, 
     RWP_DAY_WK17, 
     RWP_DAY_WK18, 
     RWP_DAY_WK19, 
     RWP_DAY_WK20, 
     RWP_DAY_WK21, 
     RWP_DAY_WK22, 
     RWP_DAY_WK23, 
     RWP_DAY_WK24, 
     RWP_DAY_WK25, 
     RWP_DAY_WK26, 
     RWP_DAY_WK27, 
     RWP_DAY_WK28, 
     RWP_DAY_WK29, 
     RWP_DAY_WK30
RESIDENT TEST_RSRWP_TEMP
WHERE GroupRow = $(a);
NEXT
 
 
FOR a = 1 to 2 //If there are two or more rows for each ROSTER_CODE before cross join, we need to change here from 2 to larger number
working_hours_temp:
CrossTable(working_hours_number, working_hours, 4)
LOAD ROSTR_CODE, 
     RWP_CYCL_LEN,
     RWP_START_DT, 
GroupRow,
     RWP_HOURS01, 
     RWP_HOURS02, 
     RWP_HOURS03, 
     RWP_HOURS04, 
     RWP_HOURS05, 
     RWP_HOURS06, 
     RWP_HOURS07, 
     RWP_HOURS08, 
     RWP_HOURS09, 
     RWP_HOURS10, 
     RWP_HOURS11, 
     RWP_HOURS12, 
     RWP_HOURS13, 
     RWP_HOURS14, 
     RWP_HOURS15, 
     RWP_HOURS16, 
     RWP_HOURS17, 
     RWP_HOURS18, 
     RWP_HOURS19, 
     RWP_HOURS20, 
     RWP_HOURS21, 
     RWP_HOURS22, 
     RWP_HOURS23, 
     RWP_HOURS24, 
     RWP_HOURS25, 
     RWP_HOURS26, 
     RWP_HOURS27, 
     RWP_HOURS28, 
     RWP_HOURS29, 
     RWP_HOURS30
RESIDENT TEST_RSRWP_TEMP
WHERE GroupRow = $(a);
NEXT
 
 
 
FOR a = 1 to 2 //If there are two or more rows for each ROSTER_CODE before cross join, we need to change here from 2 to larger number
working_date_temp:
CrossTable(working_date_number, working_date, 4)
LOAD ROSTR_CODE, 
     RWP_CYCL_LEN,
     RWP_START_DT, 
GroupRow,
RWP_DATE01,
RWP_DATE02,
RWP_DATE03,
RWP_DATE04,
RWP_DATE05,
RWP_DATE06,
RWP_DATE07,
RWP_DATE08,
RWP_DATE09,
RWP_DATE10,
RWP_DATE11,
RWP_DATE12,
RWP_DATE13,
RWP_DATE14,
RWP_DATE15,
RWP_DATE16,
RWP_DATE17,
RWP_DATE18,
RWP_DATE19,
RWP_DATE20,
RWP_DATE21,
RWP_DATE22,
RWP_DATE23,
RWP_DATE24,
RWP_DATE25,
RWP_DATE26,
RWP_DATE27,
RWP_DATE28,
RWP_DATE29,
RWP_DATE30
RESIDENT TEST_RSRWP_TEMP
WHERE GroupRow = $(a);
NEXT
 
 
DROP TABLE TEST_RSRWP_TEMP;

 

 

//3, we join these three tables together after cross join.

 
 
working_day_temp_2nd:
LOAD
RIGHT(working_week_number, 2) AS day_number,
working_week_number, 
REPLACE(working_week_day, ' ', '') AS working_week_day,
REPLACE(RWP_START_DT, '-', '') as RWP_START_DT,
GroupRow,
ROSTR_CODE, 
     RWP_CYCL_LEN
RESIDENT working_day_temp;
DROP TABLE working_day_temp;
Tag Field RWP_START_DT with '$date';
 
INNER JOIN (working_day_temp_2nd)
working_hours:
LOAD
RIGHT(working_hours_number, 2) AS day_number,
working_hours_number, 
working_hours,
REPLACE(RWP_START_DT, '-', '') as RWP_START_DT,
GroupRow,
ROSTR_CODE, 
     RWP_CYCL_LEN
RESIDENT working_hours_temp;
DROP TABLE working_hours_temp;
Tag Field RWP_START_DT with '$date';
 
 
//4, the most most most important part here below is to create a field MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS day_number_MOD. 
//This  day_number_MOD creates a single Autonumber or RowNo(),  the combination of day_number_MOD and ROSTR_CODE can refer to the roster's working hours in each cycle length date using MAPPING later. 
 
 
INNER JOIN (working_day_temp_2nd)
working_date:
LOAD
RIGHT(working_date_number, 2) AS day_number,
MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS day_number_MOD, // Interval(working_date - RWP_START_DT, 'D') is the same as NUM(working_date) - NUM(RWP_START_DT)
working_date_number, 
REPLACE(REPLACE(working_date, '0001-01-02', ''), '-', '') AS working_date,
REPLACE(RWP_START_DT, '-', '') as RWP_START_DT,
GroupRow,
ROSTR_CODE, 
     RWP_CYCL_LEN
RESIDENT working_date_temp;
DROP TABLE working_date_temp;
Tag Field working_date, RWP_START_DT with '$date';

 

 

//5, we create the mapping logic as we designed, this can be refered to in any Master calendar.

// Load mapping table from ROSTR_CODE &'|'& day_number to working_hours:
ROSTR_CODE_DAY_NUMBER_TO_HOURS:
MAPPING LOAD 
ROSTR_CODE &'|'& day_number_MOD,
working_hours
RESIDENT working_day_temp_2nd
WHERE NOT ISNULL(working_hours);
 
//6, we create Master calendar that ready to join to our roster data set. 
calendar_working_day:
LOAD DISTINCT
ROSTR_CODE, 
     RWP_CYCL_LEN,
Date(Date#(RWP_START_DT,'YYYYMMDD'),'DD/MM/YYYY') as RWP_START_DT,
     '1' AS distinct_ROSTR_CYCL_key
 
RESIDENT working_day_temp_2nd;
 
LET vMinDate = Num(MakeDate(YEAR(TODAY())-3,01,01)); //(1950,01,01) is when the first rota of cycle length started
LET vMaxDate = Num(MakeDate(YEAR(TODAY())+1,12,31));
LET vNumberOfDays = Floor($(vMaxDate)) - Floor($(vMinDate)) + 1;
LEFT JOIN (calendar_working_day)
//calendar_working_day:
LOAD Distinct
//  Num(Floor(Date)) as DateKey,
//  Date(Floor(Date)) as Date, 
  Date(Floor(Date)) as working_date,
  '1' AS distinct_ROSTR_CYCL_key,
 
//  Month(Date) as Month,
//  Date(MonthStart(Date), 'MMM YYYY') as [Month and Year],
//  Day(Date) as Day,
//  Year(YearStart(Date)) AS Year,
//  'Q'& Ceil(Month(Date)/3) AS Quarter,
//  Week(Date) as Week
  ;
 
LOAD
  Date(Num($(vMaxDate)) - RecNo() + 1) as Date
AutoGenerate($(vNumberOfDays));
 

 

//7, finally we generate the full list of working hours for every date in the Master calendar for each ROSTR_CODE, the full list of working hours follows the pattern of the roster cycle length the same as the original start date. We get the MOD here similar to what we have done in the above mapping. Be awared the MOD here is the full roster cycle length repeated MOD for the whole Master calendar. 

//The MOD we creates here is a repeate Autonumber or RowNo(),  the combination of MOD and ROSTR_CODE can refer to the roster's working hours in each cycle length date using APPLYMAP. 

//MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS MOD, // Interval(working_date - RWP_START_DT, 'D') is the same as NUM(working_date) - NUM(RWP_START_DT)
// APPLYMAP('ROSTR_CODE_DAY_NUMBER_TO_HOURS', ROSTR_CODE &'|'& MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) ,'') As working_hours_full_length,
 
LEFT JOIN (calendar_working_day)
LOAD
day_number,
day_number_MOD,
working_week_number, 
working_week_day,
working_hours_number, 
working_hours,
working_date_number, 
Date(Date#(working_date,'YYYYMMDD'),'DD/MM/YYYY') as working_date,
Date(Date#(RWP_START_DT,'YYYYMMDD'),'DD/MM/YYYY') as RWP_START_DT,
GroupRow,
ROSTR_CODE, 
     RWP_CYCL_LEN
RESIDENT working_day_temp_2nd
ORDER BY working_week_number ASC;
DROP TABLE working_day_temp_2nd;
 
 
working_day:
LOAD
day_number,
day_number_MOD,
working_week_number, 
working_week_day,
MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) AS MOD, // Interval(working_date - RWP_START_DT, 'D') is the same as NUM(working_date) - NUM(RWP_START_DT)
 
working_hours_number, 
working_hours,
 APPLYMAP('ROSTR_CODE_DAY_NUMBER_TO_HOURS', ROSTR_CODE &'|'& MOD( Interval(working_date - RWP_START_DT, 'D'), RWP_CYCL_LEN) ,'') As working_hours_full_length,
working_date_number, 
working_date,
RWP_START_DT,
GroupRow,
ROSTR_CODE, 
ROSTR_CODE AS roster_ref,
     RWP_CYCL_LEN
RESIDENT calendar_working_day
ORDER BY working_date ASC;
DROP TABLE calendar_working_day;

 

 

Below is the output result table, the fields ROSTR_CODE, working_hours_full_length, working_date are the target fields we want, this can be link of map to other employee's id and calander to find out what their working hours is in which date of a certain period.

The MOD field represnet the method to repeate the Autonumber or RowNo() for a rolling period of date using MOD.

 

 

ROSTR_CODE RWP_CYCL_LEN RWP_START_DT day_number day_number_MOD MOD working_hours_full_length working_hours working_hours_number working_week_day working_week_number working_date_number working_date GroupRow
BF0031 7 13/02/2022 - - 1 0 - - - - - 30/01/2022  
BF0031 7 13/02/2022 - - 2 7.5 - - - - - 31/01/2022  
BF0031 7 13/02/2022 - - 3 7.5 - - - - - 01/02/2022  
BF0031 7 13/02/2022 - - 4 0 - - - - - 02/02/2022  
BF0031 7 13/02/2022 - - 5 7.5 - - - - - 03/02/2022  
BF0031 7 13/02/2022 - - 6 0 - - - - - 04/02/2022  
BF0031 7 13/02/2022 - - 7 0 - - - - - 05/02/2022  
BF0031 7 13/02/2022 - - 1 0 - - - - - 06/02/2022  
BF0031 7 13/02/2022 - - 2 7.5 - - - - - 07/02/2022  
BF0031 7 13/02/2022 - - 3 7.5 - - - - - 08/02/2022  
BF0031 7 13/02/2022 - - 4 0 - - - - - 09/02/2022  
BF0031 7 13/02/2022 - - 5 7.5 - - - - - 10/02/2022  
BF0031 7 13/02/2022 - - 6 0 - - - - - 11/02/2022  
BF0031 7 13/02/2022 - - 7 0 - - - - - 12/02/2022  
BF0031 7 13/02/2022 01 01 1 0 0 RWP_HOURS01 7 RWP_DAY_WK01 RWP_DATE01 13/02/2022  
BF0031 7 13/02/2022 02 02 2 7.5 7.5 RWP_HOURS02 1 RWP_DAY_WK02 RWP_DATE02 14/02/2022  
BF0031 7 13/02/2022 03 03 3 7.5 7.5 RWP_HOURS03 2 RWP_DAY_WK03 RWP_DATE03 15/02/2022  
BF0031 7 13/02/2022 04 04 4 0 0 RWP_HOURS04 3 RWP_DAY_WK04 RWP_DATE04 16/02/2022  
BF0031 7 13/02/2022 05 05 5 7.5 7.5 RWP_HOURS05 4 RWP_DAY_WK05 RWP_DATE05 17/02/2022  
BF0031 7 13/02/2022 06 06 6 0 0 RWP_HOURS06 5 RWP_DAY_WK06 RWP_DATE06 18/02/2022  
BF0031 7 13/02/2022 07 07 7 0 0 RWP_HOURS07 6 RWP_DAY_WK07 RWP_DATE07 19/02/2022  
BF0031 7 13/02/2022 - - 1 0 - - - - - 20/02/2022  
BF0031 7 13/02/2022 - - 2 7.5 - - - - - 21/02/2022  
BF0031 7 13/02/2022 - - 3 7.5 - - - - - 22/02/2022  
BF0031 7 13/02/2022 - - 4 0 - - - - - 23/02/2022  
BF0031 7 13/02/2022 - - 5 7.5 - - - - - 24/02/2022  
BF0031 7 13/02/2022 - - 6 0 - - - - - 25/02/2022  
BF0031 7 13/02/2022 - - 7 0 - - - - - 26/02/2022  
BF0031 7 13/02/2022 - - 1 0 - - - - - 27/02/2022  

 

 

339 Views
0 Likes
0 Replies