topic Re: populate if null in QlikView

populate if null

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am hoping this is going to be simple.

I need something which will populate a field if null.

if([Level]='', 'Routine', [Level]) works as an expression but not in the script and I need it in the script so that it applies to the whole dashboard.

My only issue is that the field doesn't actually have any blank field but due to my keys it is not 1:1. This is when I would usually suppress the nulls but I am getting the right output with the blanks. I just need them populate.

I hope this made sense?

Re: populate if null

avinashelite — Tue, 24 Apr 2018 10:39:34 GMT

Try like this

if(len(trim([Level]))=0, 'Routine', [Level]) as [Level]

Re: populate if null

Anonymous — Tue, 24 Apr 2018 10:42:43 GMT

This did not work

Re: populate if null

Peter_Cammaert — Tue, 24 Apr 2018 10:47:10 GMT

That should have worked for a column containing

NULL values
Empty strings (eg '')
Whitespace strings (eg ' ')

If it doesn't work then what does your field actually contain?

Re: populate if null

Anonymous — Tue, 24 Apr 2018 10:54:27 GMT

This is an example.

As mentioned, the actual field isn't blank, but when linked to other data it is. Such as below

Re: populate if null

Peter_Cammaert — Tue, 24 Apr 2018 11:05:45 GMT

You're not trying to stuff multiple LEvel values in a single table cell, are you? Like adding an expression like =Level to a Straight table?

Those NULL values aren't really there. They are caused by QlikView trying to put two different Level values in a single cell. And that won't work (implied Only() call)

Re: populate if null

Anonymous — Tue, 24 Apr 2018 11:09:54 GMT

I am not.

I have 65 rows of an ID and only 31 of those have a level assigned to them, I want the blanks to be filled with 'Routine'

Re: populate if null

sasiparupudi1 — Tue, 24 Apr 2018 11:36:51 GMT

May be try

if(IsNUll(Aggr(Max(Level),Severity,Urgency,Age)),'Routine',Level)

Re: populate if null

Anonymous — Tue, 24 Apr 2018 11:38:33 GMT

Will this work in the script?

Re: populate if null

sasiparupudi1 — Tue, 24 Apr 2018 11:43:15 GMT

no, use group by

Re: populate if null

stigchel — Tue, 24 Apr 2018 12:04:58 GMT

Add the Level data to the table where you have the 65 rows of id using a mapping load and applymap, the third parameter of applymap is used when there is an id missing

something like this

LevelMap:

Mapping Load * Inline [id,Level

1,Essential

2,Emergency

];

OtherData:

Load *,ApplyMap('LevelMap',id,'Routine') as Level;

Load * Inline [id,Severity, Age

1, 8, 35 Days

2, 10, 2 Days

3, 4, 4 Days

];

Re: populate if null

avinashelite — Tue, 24 Apr 2018 12:52:22 GMT

how your identifying that ID have not assigned with level ? what's the logic ?

Actually this is miss data scenario..share the sample data will try to get the results

Re: populate if null

Anonymous — Tue, 24 Apr 2018 13:20:31 GMT

Example is attached.

Looking through that would be able to to fill the nulls with certain criteria?

e.g.

If null and severity is between 1-3 then Non-Essential, 4-6 then Routine, 6-8 then Urgent and 8-10 Emergency.

If that is not possible just routine will be fine.

I also need this in the script as i want it to apply to the whole dashboard

Re: populate if null

Peter_Cammaert — Tue, 24 Apr 2018 13:35:50 GMT

In your example, you can replace the current LOADs with these:

Age:

LOAD VulID,

Name,

Severity,

Age

FROM [..\..\Data\FlatFiles\age.xlsx] (ooxml, embedded labels, table is Sheet1);

Rank:

LOAD VulID,

VulID AS TempVulID,

Level

FROM [..\..\Data\FlatFiles\RankingExam.xlsx] (ooxml, embedded labels, table is Sheet1);

CONCATENATE (Rank)

LOAD VulID,

VulID AS TempVulID,

Pick(Div(Severity-1, 3)+1, 'Non-Essential', 'Routine', 'Urgent', 'Emergency') AS Level

RESIDENT Age

WHERE Not Exists(TempVulID, VulID);

DROP Field TempVulID;

[Edit] Swapped the default 'Routine' assignment for four different values.

Re: populate if null

Peter_Cammaert — Tue, 24 Apr 2018 13:48:05 GMT

BTW you cannot replace what doesn't exist. You can only create additional data where values are missing.

The null values indicate that the Level data in table Rank is missing for those last few IDs.

The script code in the previous post just adds rows for all missing IDs.

You could also do that using a Mapping Table and a simple ApplyMap call, like (replace everything in your example script by this):

MapRank:

MAPPING

LOAD VulID,

Level

FROM [..\..\Data\FlatFiles\RankingExam.xlsx] (ooxml, embedded labels, table is Sheet1);

Age:

LOAD VulID,

Name,

Severity,

Age,

ApplyMap('MapRank', VulID,

Pick(Div(Severity-1, 3)+1,

'Non-Essential', 'Routine', 'Urgent', 'Emergency')) AS Level

FROM [..\..\Data\FlatFiles\age.xlsx] (ooxml, embedded labels, table is Sheet1);

Re: populate if null

sasiparupudi1 — Tue, 24 Apr 2018 18:49:21 GMT

one solution may be