Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Replacing 0 for '-' Qlik sense

Hi,

I want to to replace '-' as Zero. 

Kindly find the below image for reference.

In my source not having data for Product C & D for the month 2019-oct, i want to replace '-' as zero.

Capture.JPG

Thanks!!!

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Did you remove a last post? I'm replying to that one.

 

If this really is a big issue then your problem should be solved a bit differently and in the data load editor.

  1. Create all year month combinations
  2. Left join your fact table to the year month combinations
  3. For the field with your values, applymap these values and if there is no value, return a 0.

What you have now are values for all year month combination, also if you originally didn't had any. This will be filled with 0's. You will now be able to make this good MOM.

Yes it can be solved otherwise with a lot of IF statements, but these will make your report slow.

Jordy

Climber

Work smarter, not harder

View solution in original post

6 Replies
Highlighted
Partner
Partner

Hi Nivetha,

Because there are no values, Qlik is returning null(), but Qlik Sense doesn't offer a replace option like in QlikView.

You can use the following:

IF(isnull(Sum(result)),0,Sum(result))

Jordy

Climber 

Work smarter, not harder
Highlighted
Creator II
Creator II

Thanks  Jordywegman,

 

In set expression, Is there is option to find that for Product C & Oct-2019 not having data in source.

Highlighted
Partner
Partner

What do you mean exactly? You can replace your measure with the one I have proposed and use your metrics/fields here. 

You can find in your data model viewer that you will have a subset ratio that is not 100% because some combinations are missing.

Jordy

Climber

Work smarter, not harder
Highlighted
Partner
Partner

Did you remove a last post? I'm replying to that one.

 

If this really is a big issue then your problem should be solved a bit differently and in the data load editor.

  1. Create all year month combinations
  2. Left join your fact table to the year month combinations
  3. For the field with your values, applymap these values and if there is no value, return a 0.

What you have now are values for all year month combination, also if you originally didn't had any. This will be filled with 0's. You will now be able to make this good MOM.

Yes it can be solved otherwise with a lot of IF statements, but these will make your report slow.

Jordy

Climber

Work smarter, not harder

View solution in original post

Highlighted
Creator II
Creator II

Hi Jordy,

Kindly help me with if condition.

Highlighted
Partner
Partner

Hi,

There is not really an if statement, because we are using the functionalities of the applymap

ApplyMap('YourApplyMapYouMade,[FieldUsedForLookUp],0)

The 0 is what you return if the statement can't find your [FieldUsedForLookUp].

Jordy

Climber

Work smarter, not harder