Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Elarbe
Contributor II
Contributor II

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

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:

Elarbe_0-1664350814231.png

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

Elarbe_1-1664350927453.png

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?

 

Labels (5)
3 Replies
canerkan
Partner - Creator II
Partner - Creator II

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

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
Partner - Creator II
Partner - Creator II

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...