Remove or Replace Null Values

[tan_chungkam]

Hi All,

I think I'm repeating post the same question here.

I'm looking for a way to remove or replace Null Values in Technician (Highlighted in red box) with showing full days (1st - 31st of every month).

[miskinmaz]

Hi,

You can try the uncheck the Include Null Value checkbox.

[tan_chungkam]

@miskinmaz 

Thanks for the advice.

After I unchecked the Include Null Value checkbox, it is not showing full date as highlighted at below.

I would like to keep the full date.

[miskinmaz]

So you can replace the null value using the if condition like:

If(isnull(Field1),'NA',Field1).

[tan_chungkam]

@miskinmaz ,

It does not work.

I would suggest you to have a try on my files first then only giving advice.

[cpomeren003]

Interesting problem. I think you can solve it two ways:

1. You can add a dummy technician named: "Calendar" or "Workdays" that consists of all the dates you want to see (start date 01/01/2018 and end date 12/31/2018 etc.) and then just give it a different color so it also doesn't look out of place.
2. You can bridge all the gaps between the technician dates. So for example:
   
   

   Technician	Start Date	End Date	Working/Planned
   A	01/02/2018	05/02/2018	Yes
   A	06/02/2018	14/02/2018	No
   A	15/02/2018	01/03/2018	Yes

   
   I would add the second row and then color based on "Working/Planned" so it still looks good.

Hope it helps!

[tan_chungkam]

@cpomeren003 ,

Thanks for suggestion.

Appreciate if you show me the way of doing it. 

[cpomeren003]

I quickly threw something together to show solution 1. It can be enhanced, but it shows what I mean.

I changed the underlying data to this (Testing.xlsx):

Technician Start Date End Date
Calendar 1-1-2018 30-4-2018
A 1-2-2018 5-2-2018
A 15-2-2018 1-3-2018
A 1-4-2018 2-4-2018
B 3-1-2018 10-1-2018
B 5-3-2018 8-3-2018
B 1-4-2018 30-4-2018
C 1-2-2018 28-2-2018
C 5-3-2018 6-3-2018
C 1-1-2018 2-1-2018

Hope it helps!

[tan_chungkam]

@cpomeren003 ,

Thanks for showing the way.

Just a question. So this has to be done in the data source(excel file)?

If so, then i might not be favour on this method.

[cpomeren003]

No, you can do it just as easily in the script, I just did it the quick and easy way and used the provided Excel.