Using Peek() to fill missing data although the records aren't identical

[Elarbe]

Hello all,

I have a large table that, when filtering on one user, resembles something as shown below, let's call this table Example:

Example:

I would like to eliminate the nulls for whichever user(s) I want to look at:

I'm trying to use Peek() but having some difficulties. This is what I thought might have worked:

IF(IsNull(Title), Peek('Title', 0, 'Example'), Title) as CompletedRecords

Does anyone see where I'm going wrong or have an elegant solution or know what the best practice would be?

[canerkan]

Hi Elarbe,

in your example, everyone has the the Title "Manager" and has "Earth" as location. So if you want to insert only one Value into a field you can replace the line where you are loading the specific field and simply type the string. Like this:

'Manager' as Manager

or

'Earth' as Location

Therfore you don't need to use any inter-record functions.

Regards

Can

[Elarbe]

Hi Canerkan,

thank you for your reply.

I think I didn't explain the problem well enough initially.

This is just an example for one user in the table. In the real table there are hundreds of thousands of users. Basically anything with type c for any given user is showing as null. I need to "take over" the Title and Location values from the types a and b for every user in the table.

I hope this makes more sense. It has to be dynamic because it's just not feasible to manually go through that many records and it's possible that one of these things will change in the future and have to be updated accordingly.

[canerkan]

I guess the beste way to do that would be by mapping the values. As long as you have at least one complete record. It's similiar to the VLookup function in Excel.

If the values change, are all records of user affected or just the ones that are being created after the change?

To map the values you have to create mapping table first. like this:

mapTitle:
Mapping Load Distinct
User,
Title
From.....
where Title <> Null();

check this link to get more information to this step:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

in the next Step you will have to apply this map on the field you want to apply the value of your ma on. Like this:

Table:
Load
....
Applymap('mapTitle', User, 'No complete Record found') as Title
From.....

check this link to get more information to this step:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/MappingFunctio...