Replace missing with 'Data Missing'/N/A

[PrashantRupani]

The data I have  looks like this

Table_1:

Emp_ID

101,

102,

103

Table 2: 

Emp_ID, Region 1 Manager, Region 2 Manager, Region 3 Manager

101   , John, Dave , 

102,  Joe, 

Emp 101 has Region 1 Manager as John and Region 2 Manager as Dave. Since 101 are themselves a Region 3 Manager hence there is no data populated for that. In such cases , I would like to display 'N/A'.

But for 103, There is no manager in any of the columns. So it should say 'Data Missing' in such case.

The final data should look like - How should I implement this in load script. Is this possible in load script or chart expression or both

Emp_ID, Region 1 Manager, Region 2 Manager, Region 3 Manager

101   , John, Dave , N/A

102,  Joe, N/A, N/A

103, Data Missing, Data Missing, Data Missing

[MayilVahanan · Accepted solution]

HI @PrashantRupani 

Try like below

A:
LOAD * INLINE [
Emp_ID
101
102
103
];
B:
LOAD Emp_ID,Emp_ID as E1, If(trim(len([Region 1 Manager])) = 0, 'N/A', [Region 1 Manager]) as [Region 1 Manager]
, If(trim(len([Region 2 Manager])) = 0, 'N/A', [Region 2 Manager]) as [Region 2 Manager]
, If(trim(len([Region 3 Manager])) = 0, 'N/A', [Region 3 Manager]) as [Region 3 Manager] INLINE [
Emp_ID, Region 1 Manager, Region 2 Manager, Region 3 Manager
101 , John, Dave,
102, Joe,
];

Concatenate(B)
Load Emp_ID, 'Data Missing' as [Region 1 Manager], 'Data Missing' as [Region 2 Manager], 'Data Missing' as [Region 3 Manager] Resident A Where not Exists(E1, Emp_ID);

DROP Field E1;

If you're data has null values instead of missing values for Regional manager, try like below

NullAsValue [Region 2 Manager],[Region 3 Manager];

Set NullValue = 'N/A';

Replace inline table with ur source

[PrashantRupani]

This worked. Thanks @MayilVahanan