topic Re: How do I remove the false option within an If Statement? in QlikView

How do I remove the false option within an If Statement?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am working on pulling through some stock data (see script below) from our ERP systems. I wanted to create an easy group within the transform so I can just pull through the 'Inventory group' to display individual values of Finish, Raw, WIP goods. However as with any if statement it requires a false option of Other. At the moment this is making my graphs look untidy as there is a value of 'zero' and 'negative zero' as Other, displayed on my pie chart and line chart. Is there any way to block out the false or Other option?

Many thanks in advance!

If([Inventory Type]='E1 Finished Goods','Finished',

If([Inventory Type]='E1 Raw Materials','Raw',

If([Inventory Type]='E1 In Process','WIP','Other')

)

) as [Inventory Group]

Re: How do I remove the false option within an If Statement?

marcus_malinow — Wed, 23 Aug 2017 15:16:45 GMT

Hi Ali,

I'm assuming here that the [Inventory Group] field is being created in your load script.

To filter it from your charts, use set analysis. For example, if you have a sum of Sales measure, express it like so:

sum({$<[Inventory Group]-={'Other'}>} Sales)

Marcus

Re: How do I remove the false option within an If Statement?

Peter_Cammaert — Wed, 23 Aug 2017 15:17:21 GMT

Every IF() call can be supplied with only two parameters (a logical expression and a True-value)

If in your example you omit the 'Other' value altogether, three failing IFs will result in a NULL value being stored in field [Inventory Group].

A bar chart or a straight table can be told to "forget" about these NULL dimension values, i.e. not include them in the display. Is that what you want?

Re: How do I remove the false option within an If Statement?

chris_johnson — Wed, 23 Aug 2017 15:24:10 GMT

Hi,

You do not need to specify an "else" condition in QlikView. If you do not it will have a "null" value instead.

This would be the same as changing "Other" for "null()":

If([Inventory Type]='E1 Finished Goods','Finished',

If([Inventory Type]='E1 Raw Materials','Raw',

If([Inventory Type]='E1 In Process','WIP',null())

)

) as [Inventory Group]

You would need to check the properties of your chart to make sure that null values are being hidden (In the Dimensions tab make sure "Suppress Null Values" is ticked for Inventory Group).

The one advantage of Marcus' solution though is that you will be able to select "Other" as a value, whereas if it is null then you can't select it.

Hope that helps

Re: How do I remove the false option within an If Statement?

sasiparupudi1 — Wed, 23 Aug 2017 18:07:49 GMT

Use pick match combination

Pick (Match ([Inventory Type],'E1 Finished Goods', 'E1 Raw Materials','E1 In Process'),'Finished','Raw',WIP')

as [Inventory Group]