Replace null values with previous numbers

Question

I'm trying to get previous numbers for the highlighted period, but the below doesn't work.

If(Len(Request) = 0 , Peek(NewRequest), Request) as NewRequest

This is how its looks at the front end in a straight table.

enter image description here

Answer 1

How are you trying to create the field?

Im using the below code:

RawData:
Load 
  *,
  If(Len(Request) = 0 , Peek(NewRequest), Request) as NewRequest
;
Load * Inline [
EmpID, Period  , Employee, Request
AA   , 1/1/2023, Yellow  , 6
AB   , 2/1/2023, Brown   , 16
AC   , 3/1/2023, Pink    , 12
AA   , 2/2/2023, Yellow  , 5
AB   , 2/1/2023, Brown   , 
AC   , 3/1/2023, Pink    , 
];

And im getting the below data. As you can see the NewRequest column is correctly populated.

Answer 2

Update after clarification

In order to see those records in a straight table in the frontend, you'll need to include some dimension field that can uniquely identify each record. Since there are aggregations in the table (=Sum([Request]) and =Sum([NewRequest])) records are essentially just grouped on the available dimension fields. Hence, when Qlik sees two records with AB, 2/1/2023, Brown it has nothing to differentiate them, so they're grouped or "collapsed" into one record.

The fix for that is to add a record identifier or, at the least, a more granular key field to differentiate and identify unique records. One of the myriad ways you can achieve this is to use the RowNo() function to create a new ID field in the data load editor, like so:

[RawData New]:
Load
    [Key]
  , [EmpID]
  , [Period]
  , [Employee]
  , [Request]
  , If( IsNull(EmptyIsNull(Request)), ApplyMap('RequestMap', [Key]), [Request]) as NewRequest
  
  , RowNo() as RowID   // <----- Our new record ID field
  
Resident RawData;

Then just add that field to your table in the dashboard:

If you wanted to do a pure-frontend method, you could try to get fancy with the Aggr() function and the NODISTINCT keyword but it would probably be more hassle than it'd be worth.

---

Original answer

If you're trying to get the [Request] values for the previous [Period] + [Employee] records, then you have a few options:

Option 1: Lookup() function

You can use the Lookup() function to find a value in an already-loaded table, including the current table, given a lookup field, lookup value, and return field.

Here's the script you can use:

RawData:
 // Part 3
Load 
  *,
  Coalesce(EmptyIsNull(Request), Lookup(Request, Key, Key)) as NewRequest
;
 // Part 2
Load *,
    AutoNumber(EmpID & '|' & Period) as Key
;
 // Part 1
Load * Inline [
EmpID, Period  , Employee, Request
AA   , 1/1/2023, Yellow  , 6
AB   , 2/1/2023, Brown   , 16
AC   , 3/1/2023, Pink    , 12
AA   , 2/2/2023, Yellow  , 5
AB   , 2/1/2023, Brown   , 
AC   , 3/1/2023, Pink    , 
];

What's happening here:

Part 1

• Load the data.

Part 2

• Here we use the AutoNumber() function to create a key field. The function works by returning an incrementing number starting with 1 for a unique concatenation of fields/values. In this case, we get a unique key for each concatenation of the [EmpID] and [Period] fields. We use & '|' & to combine the field values with a pipe | symbol as a best practice.

Part 3

• The EmptyIsNull() function is a shorthand for treating empty (Len([SomeField]) = 0) values as Null. So here, the empty values in the [Request] field will be treated as Nulls.
• The Coalesce() function is a shorthand for If(Not isNull([SomeField]), [SomeField], 'Something else...'). So basically it's saying "If the [Request] field is not null, use that field. If it is null, however, use the value returned by Lookup(...)."
• The Lookup() function is what we use to essentially "look backwards" at the records we've already loaded into our table. So when we use Lookup(Request, Key, Key), what we're saying is "In the [Key] field, look up the [Key] value for the current record and then return the value you find from the [Request] field.

That should all result in Qlik looking for and finding the correct [Request] values when they are missing:

Something to note about the Lookup() function is that it can slow down your data load when you have a lot of records. The next option would likely be faster.

Option 2: Mapping load prefix and ApplyMap() function

Your other option is to use the Mapping load prefix and ApplyMap() function to get those previous values.

Here's the script we'll look at:

RawData:
 // Part 2
Load *,
    AutoNumber(EmpID & '|' & Period) as Key
;
 // Part 1
Load * Inline [
EmpID, Period  , Employee, Request
AA   , 1/1/2023, Yellow  , 6
AB   , 2/1/2023, Brown   , 16
AC   , 3/1/2023, Pink    , 12
AA   , 2/2/2023, Yellow  , 5
AB   , 2/1/2023, Brown   , 
AC   , 3/1/2023, Pink    , 
];


[RequestMap]:
 // Part 3
Mapping Load Distinct
    [Key]
  , [Request]
Resident RawData
  Where Not IsNull( EmptyIsNull([Request]) )
;


[RawData New]:
 // Part 4
Load
    [Key]
  , [EmpID]
  , [Period]
  , [Employee]
  , [Request]
  , If( IsNull(EmptyIsNull(Request)), ApplyMap('RequestMap', [Key]), [Request]) as NewRequest
Resident RawData;

 // Part 5
Drop Table [RawData];

Here's an explanation of things:

Part 1

• Load the data.

Part 2

• Here we use the AutoNumber() function to create a key field. See the explanation in the previous option.

Part 3

• Here we use the Mapping load prefix to load a Mapping table, which has only two columns, the first of which is the field we "match" on, and the second of which is the field we replace those values with. In our case, our two columns are the [Key] field, whose values will be replaced, and the [Request] field, whose values will be replacing those in the [Key] field. So you can see how it's similar to the Lookup() function from the previous option, as we are looking for a field to match on and then loading the values from another resulting field.
• Note that we use the Distinct keyword as well, which tells Qlik to only load distinct records. That's because a mapping table only needs unique "match-and-replace" options.
• We also use the Where clause to filter out the records with empty [Request] values. The expression Where Not IsNull( EmptyIsNull([Request]) ) uses the EmptyIsNull() function (explained in the previous option) and the IsNull() function to check for, and filter out, the records with empty [Request] values.

This is what our [RequestMap] table looks like underneath the hood:

NOTE: I messed up the above screenshot, just pretend those columns are switched. You want to think of it as "match" field first and then the "replace" field second.

Part 4

Here we are loading a new table called [RawData New] -- the important part of this table load is this expression here:

If( IsNull(EmptyIsNull(Request)), ApplyMap('RequestMap', [Key]), [Request]) as NewRequest

Here's what it's doing:

• First, in our If() statement, we check to see if the [Request] field is empty/null for the current record. If it is, we use the ApplyMap() function to "map over" the values of the [Key] field using the [RequestMap] table we created previously using the Mapping load. So basically Qlik will take the [Key] value for the current field, search the first column of the [RequestMap] table for that value, and if it finds it, it will return the value in the second column of the [RequestMap] table. If it doesn't find a matching value in the first column, it will just return Null. If, however, the [Request] field is not empty/null for the current record, it will simply return the value found in the [Request] field.

Part 5

• Here we Drop the original [RawData] table since we no longer need it -- we now have the [RawData New] table that has all of our data, including the new [NewRequest] field.

This should result in what we want: