Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
EderMachado
Contributor II
Contributor II

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

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. 

 

image.png

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! 

 

1 Solution

Accepted Solutions
chrismarlow
Specialist II
Specialist II

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.

 

View solution in original post

5 Replies
chrismarlow
Specialist II
Specialist II

Hi,

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

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
Contributor II
Contributor II
Author

@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
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

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!