QLIK SENSE AutoNumber reset to 1 if condition met

[NellyAcko]

Hi All

I have a data load that uses AutoNumber to count the numbers of days of sickness. What I need it to do is only count the consecutive days and then reset. So if a person returns to work for a regular shift then goes back on sick leave I need the AutoNumber process to restart at 1. 

Maybe the AutoNumber is not the best approach but I cannot seem to work it out. 

if(Wildmatch([Pay_Type], 'Sickness'), AutoNumber(recno(),[EMP_Code] & [Pay_Type])) as Occurrence,

This is the data and the last field is the result of the expression above. Data Row 19 below 25/01/2022 shows Occurrence 7 but I need to to show as 1 due to the person working  5 regular/transfer shifts (17th to 24th) after previous 5 days of sickness occurrence. 

LOAD * INLINE
[

Date,EMP Code,Pay Type,Type,JOB TYPE,Hours Worked,Hours Not Worked,Sickness Occurrence
29/01/2022,S29536,Basic,Transfer,MNGRS,3,-,-
29/01/2022,S29536,Basic,Transfer,MNGRS,5,-,-
02/01/2022,S30299,Sickness,-,SALES,-,6,1
03/01/2022,S30299,Covid,-,SALES,-,8,-
06/01/2022,S30299,Covid,-,SALES,-,8,-
07/01/2022,S30299,Covid,-,SALES,-,8,-
08/01/2022,S30299,Covid,-,SALES,-,8,-
09/01/2022,S30299,Covid,-,SALES,-,8,-
10/01/2022,S30299,Sickness,-,SALES,8,8,2
13/01/2022,S30299,Sickness,-,SALES,8,8,3
14/01/2022,S30299,Sickness,-,SALES,-,8,4
15/01/2022,S30299,Sickness,-,SALES,-,8,5
16/01/2022,S30299,Sickness,-,SALES,-,8,6
17/01/2022,S30299,Basic,Regular,SALES,6,-,-
21/01/2022,S30299,Basic,Transfer,SALES,8,-,-
22/01/2022,S30299,Basic,Transfer,SALES,5.5,-,-
23/01/2022,S30299,Basic,Regular,SALES,6,-,-
24/01/2022,S30299,Basic,Regular,SALES,5.5,-,-
25/01/2022,S30299,Sickness,-,SALES,-,8,7
26/01/2022,S30299,Sickness,-,SALES,-,8,8
27/01/2022,S30299,Sickness,-,SALES,-,8,9
28/01/2022,S30299,Sickness,-,SALES,-,8,10
31/01/2022,S30299,Basic,Break,SALES,0.5,-,-
31/01/2022,S30299,Basic,Regular,SALES,2.5,-,-
31/01/2022,S30299,Basic,Regular,SALES,5.5,-,-
02/01/2022,S49649,Basic,Regular,SALES,6,-,-
05/01/2022,S49649,Basic,Regular,SALES,5,-,-
08/01/2022,S49649,Basic,Regular,SALES,5,-,-
09/01/2022,S49649,Sickness,-,SALES,-,6,1
13/01/2022,S49649,Sickness,-,SALES,-,6,2
14/01/2022,S49649,Annual Leave,-,SALES,-,6,-
16/01/2022,S49649,Annual Leave,-,SALES,-,6,-
20/01/2022,S49649,Annual Leave,-,SALES,-,4,-
21/01/2022,S49649,Sickness,-,SALES,-,6,3
23/01/2022,S49649,Sickness,-,SALES,-,6,4
24/01/2022,S49649,Sickness,-,SALES,-,6,5
29/01/2022,S49649,Basic,Regular,SALES,4,-,-

](delimiter is ',');

Many thanks in advance

[chrismarlow · Accepted solution]

Hi,

You could try a peek function, so something like the below shows this as a preceding load to your inline. Note your data will need to be sorted by employee then date.

Cheers,

Chris.

load
	If(RowNo()=1,
		If([Pay Type]='Sickness',1,0),
		if(Peek('EMP Code')<>[EMP Code],
			If([Pay Type]='Sickness',1,0),
			If([Pay Type]='Sickness',
				Peek('Alternative Sickness')+1,
				0)
			)
		) AS [Alternative Sickness],
	*;
LOAD * INLINE
[
Date,EMP Code,Pay Type,Type,JOB TYPE,Hours Worked,Hours Not Worked,Sickness Occurrence
29/01/2022,S29536,Basic,Transfer,MNGRS,3,-,-
29/01/2022,S29536,Basic,Transfer,MNGRS,5,-,-

etc

[NellyAcko]

Hi Chris

Thanks for the alternative approach, it does partly do what I need but the problem with this is all breaks are reset to 1. As an example Employee S30299 is listed as Sickness on the 02/01/22 then moves to Covid from the 3rd then back to Sickness, following that from the 10th they are back on as Sickness. In this instance they have not returned to work so in theory on the 10th the sickness count should continue showing as 2 and not restart as 1.

Required output should be only if the [Pay_Type] = 'Basic' comes between 2 'sickness' periods then the count resets to 1

I hope that makes sense.

Regds

Neil

[NellyAcko · Accepted solution]

HI Chris I managed to get this to do what I need by repeating the previous sickness count except where the Pay Type is basic which then returns a zero so the next Sickness period starts again at 1.

I changed the last false from 0) to if([Pay Type]='Basic',0,Peek('Alternative Sickness'))

Updated Code:

Load
    If(RowNo()=1,
        If([Pay Type]='Sickness',1,0),
        if(Peek('EMP Code')<>[EMP Code],
             If([Pay Type]='Sickness',1,0),
             If([Pay Type]='Sickness',
                 Peek('Alternative Sickness')+1,
                     if([Pay Type]='Basic',0,Peek('Alternative Sickness')))
                  )
             ) AS [Alternative Sickness],
*;

[chrismarlow]

Hi,

I am glad you managed to get it working.

Cheers,

Chris.

[NellyAcko]

Thanks Chris only managed this with your guidance so thank you.

Only problem I had was getting it to work in Enterprise production using a QVD file, I assumed it was to order of the employees but I had ordered the QVD before saving it. I'm wondering if the order is changed when loading it with a where clause. 

I ended up loading the data where date is >= 01/01/2022 and <=31/01/2022 in to a temp table, I then reloaded it as resident with Order by [EMP Code] in to another temp table then loading again with the preceding code and changed to RecNo() and it works perfectly 😃

Thanks again

Neil