Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tan_chungkam
Contributor

Remove or Replace Null Values

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

image.png

 

14 Replies
miskinmaz
Contributor III

Re: Remove or Replace Null Values

Hi,

You can try the uncheck the Include Null Value checkbox.

null.JPG

tan_chungkam
Contributor

Re: Remove or Replace Null Values

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

image.png

miskinmaz
Contributor III

Re: Remove or Replace Null Values

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

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

Re: Remove or Replace Null Values

@miskinmaz ,

It does not work.

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

Partner
Partner

Re: Remove or Replace Null Values

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:

    TechnicianStart DateEnd DateWorking/Planned
    A01/02/201805/02/2018Yes
    A06/02/201814/02/2018No
    A15/02/201801/03/2018Yes

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

Hope it helps!

tan_chungkam
Contributor

Re: Remove or Replace Null Values

@cpomeren003 ,

Thanks for suggestion.

Appreciate if you show me the way of doing it. 

Partner
Partner

Re: Remove or Replace Null Values

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
Contributor

Re: Remove or Replace Null Values

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

Highlighted
Partner
Partner

Re: Remove or Replace Null Values

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.