A method to repeate the Autonumber or RowNo() for ... - Qlik Community

Mr_H_Huang · ‎2023-10-20

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

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

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

//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

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

Application Development

Creating Analytics

Layout & Visualizations

Not a Question

Scripting