populate if null

[Anonymous]

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?

[stigchel]

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

];

[avinashelite]

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 

[Anonymous]

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

[Peter_Cammaert]

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.

[Peter_Cammaert]

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);

[sasiparupudi1]

one solution may be