Empty Value in "Straight Table"

[nicouek]

Hello All,

I have the table below in Qlik Sense:

I am creating an HR record search system that would allow users to search for:

*who is or was an employee's supervisor at a point in time?

or

*which employees are, or were a supervisor overseeing at a point in time?

The issue I am having is that some of the fields are retuning 'null'. 

So I wanted to ask the community; is there a way to model the data to return the previous or most recent record in the field?

LOGIC in my diagram: If SUPERVISOR_NM is null, then give me the record that was last entered the exists in that field?

Or is this "a garbage in garbage out" type of situation?

thank you

[Oleg_Troyansky]

Hi there!

You certainly can solve this situation, both in QlikView or in Qlik Sense. I can suggest two ways of doing so:

1. In QlikView, if this is loaded from a text file (spreadsheet, CSV, etc...), you have almost an automated solution in the Text File load wizard. It's called "Fill". Check it out, it's not hard to pick up the logic. In Qlik Sense, there is no wizard for that, but you can generate the load statement in QlikView (Personal Edition is free) and then copy it to Qlik Sense with minimal changes, and the same filters that work in QlikView, would work in Qlik Sense.

2. Otherwise, you can script the same behavior in either tool, using functions Peek() and Previous(). You just need to sort the table chronologically by Emp ID and Year, and then apply simple logic that compares the current value of Emp ID with the value from the prior row. Look for blog articles about using Peek() and Previous(), and you will find your solution.

Cheers,