Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
UserID2687
Contributor

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
Partner
Partner

Re: Replacing 0 for '-' Qlik sense

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
6 Replies
Partner
Partner

Re: Replacing 0 for '-' Qlik sense

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
UserID2687
Contributor

Re: Replacing 0 for '-' Qlik sense

Thanks  Jordywegman,

 

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

Partner
Partner

Re: Replacing 0 for '-' Qlik sense

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
Partner
Partner

Re: Replacing 0 for '-' Qlik sense

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
UserID2687
Contributor

Re: Replacing 0 for '-' Qlik sense

Hi Jordy,

Kindly help me with if condition.

Partner
Partner

Re: Replacing 0 for '-' Qlik sense

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