Loading only certaing values by filtering a certain date or status?

[EderMachado]

Good day QlikView gurus!

We're working on a HR development on QV, specifically a headcount report. The database we're using has a bit of... let's put it this way, "unnecesary values", making our analysis muddy. Each employe has a date and a record for each kind of movement they make, i.e. when they were hired, if they were promoted, and most importantly, when they were written off, if they were. The thing is, even if an employee is written off at any given day of the month (let's say, 09/17/2018), there is still a record for that same user at the end of the month (09/30/2018), without any kind of flag or identifier to let us know. Some users can have records even 2 or 3 months after where left the company, and again, none of those records has any kind of flag for us to filter or not load them. 

As you can see, in the column "Clase_Medida", some of the employees might have a "BAJA" status, that being the day they stopped working. What I want to do is to write in script (if possible), if a user has a last worked day date (determined by the Clase_Medida field), then stop loading any more values for that user, but only for that user. So, no matter if they have 3, 4 or 50 more records, if at any point they have a "BAJA" status, then stop loading records that have a date greater than the "last worked" date (BAJA status).

Is there any way something can be done to solve this problem? Thanks for reading! 

[chrismarlow]

Hi,

It is pretty clear you should stop people 'leaving' … 

However, you could try adapting this toy script;

data:
LOAD * INLINE [
    Nombre_Empleado, Clase_Medida, %Fetcha
    A, Sin asignar, 01/01/2019
    A, Sin asignar, 01/02/2019
    B, Sin asignar, 01/01/2019
    B, BAJA, 15/01/2019
    B, Sin asignar, 01/02/2019
];

left join (data)
Load
	Nombre_Empleado,
	%Fetcha AS %Fetcha_BAJA
resident data
where Clase_Medida='BAJA';

data_final:
NoConcatenate
Load
	Nombre_Empleado,
	Clase_Medida, 
	%Fetcha
resident data
where IsNull(%Fetcha_BAJA) OR %Fetcha<=%Fetcha_BAJA;

drop table data;

Cheers,

Chris.

[rubenmarin]

Hi, you can load data sorted by %Fecha, when 'Nombre_empleado has a Clase_Medidad='BAJA' store the value in a field to check with Exists(),ie:

LOAD
Nombre_Empleado,
Clase_Medida,
If(Clase_Medida='BAJA', Nombre_Empleado) as chkEmpleadoBaja,
// Other fields
Resident DataTable
Where not exists(chkEmpleadoBaja, Nombre_Empleado) // If nombre_Empleado is loaded in chkEmpleadoBaja don't load it anymore
Order by Nombre_Empleado, %Fecha;

[EderMachado]

@chrismarlow 

Heh, right? Let's not let them leave the company or fire them, easier for HR! Hahaha!

Thank you for your answer, the script really helped, and it's pretty much spot on to what we need, but after applying it I found out 2 things:

1.- If %Fecha and %Fecha_BAJA are exactly the same value, the value will still get loaded, so I guess the "=" in the "<=" conditional part is not validating properly or somesuch

2.- There's a couple of cases where an employee leaves the company (BAJA status), but at some point in the future they can come back (REINGRESO status), maybe months or years after they first left, so if at any given point there's a "REINGRESO" status, we have to ignore the first "BAJA" and actually load them... yeah, convoluted.

Anyway, if you could help me with this I would appreciate it so much! Thanks for your answer! 

[chrismarlow · Accepted solution]

Hi,

I actually prefer @rubenmarin approach, but maybe not with the extra complexity you describe & suspect you would always need a second load.

So a few changes, but possibly not the end as maybe you need to think about what to do with records between BAJA and REINGRESO & maybe you get a BAJA, REINGRESO, BAJA, REINGRESO (I am part way through that cycle … :))

So to exclude the BAJA records I think I would specifically exclude but leave the <= as is. I would add a max & group to the join, to avoid duplicating records for multiple BAJA & add code for REINGRESO that is very similar.

I t then gets a bit horrible as I tried to amend the where clause, but gave up and ended up with the nasty embedded If statement in the additional left join.

data:
LOAD * INLINE [
    Nombre_Empleado, Clase_Medida, %Fetcha
    A, Sin asignar, 01/01/2019
    A, Sin asignar, 01/02/2019
    B, Sin asignar, 01/01/2019
    B, BAJA, 15/01/2019
    B, Sin asignar, 01/02/2019
    C, Sin asignar, 01/01/2019
    C, BAJA, 10/01/2019
    C, REINGRESO, 20/01/2019
    C, Sin asignar, 01/02/2019
    C, BAJA, 10/02/2019
    C, Sin asignar, 20/02/2019
];


left join (data)
Load
	Nombre_Empleado,
	Max(%Fetcha) AS %Fetcha_BAJA
resident data
where Clase_Medida='BAJA'
group by Nombre_Empleado;

left join (data)
Load
	Nombre_Empleado,
	Max(%Fetcha) AS %Fetcha_REINGRESO
resident data
where Clase_Medida='REINGRESO'
group by Nombre_Empleado;

left join (data)
Load distinct
	Nombre_Empleado,
	%Fetcha,
	if(IsNull(%Fetcha_BAJA),
		1,
		if(IsNull(%Fetcha_REINGRESO),
			If(%Fetcha<=%Fetcha_BAJA,1,0),
			If(%Fetcha_REINGRESO>%Fetcha_BAJA,
				1,
				If(%Fetcha<=%Fetcha_BAJA,1,0)
				)
			)
		) AS Include
resident data
where Match(Clase_Medida,'BAJA','REINGRESO')=0;

data_final:
NoConcatenate
Load
	Nombre_Empleado,
	Clase_Medida, 
	%Fetcha
resident data
where Include=1 AND Match(Clase_Medida,'BAJA','REINGRESO')=0;

drop table data;

I have a feeling this could end with a interval match, but not on a Friday night.

Cheers,

Chris.

[EderMachado]

Thank you so much! Your script did the trick! I'm so grateful you took your time to answer me, seriously thanks! Have a nice weekend!