Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
amazon_mckenzie
Contributor II
Contributor II

Assigning a field to be Null- so it doesn't show up

Assume my data set looks like the following:

Item                    Type

Shirt                    Red

Shirt                    Blue

Socks                  Red

Shoes                 Green

Tie                       Purple

Tie                       Red

Socks                  Yellow

I want to create a flag in the script which would allow me to use Type as a dimension, but only for certain colors. For example, I want Red and Blue to show as 'Primary', and Purple to show as 'Secondary', and for the flag to completely ignore Green or Yellow or any other Types.

I tried doing something like:

If(Type = 'Red', 'Primary', if (Type = 'Blue', 'Primary', if(Type = 'Purple', 'Secondary', ' '))) as TypeFlag

But that doesn't work- the other Types that I didn't want to Flag still are measured when I use TypeFlag as a dimension. For example, if I use the expression count(Item) I see a chart that displays:

Primary          4

Secondary     1

                      2

This is even the case when I have 'Suppress when Value is Null' etc. selected.

What am I missing? How can I assign a Null value to Green and Yellow, such that when I use TypeFlag as a dimension, those items are ignored?

1 Solution

Accepted Solutions
hector_munoz
Specialist
Specialist

Hi Crystal,

Use Null() function instead of ' ' (space) and supress null values in chart. It should work...

If(Type = 'Red', 'Primary', if (Type = 'Blue', 'Primary', if(Type = 'Purple', 'Secondary', Null()))) as TypeFlag;

Regards,

H

View solution in original post

3 Replies
hector_munoz
Specialist
Specialist

Hi Crystal,

Use Null() function instead of ' ' (space) and supress null values in chart. It should work...

If(Type = 'Red', 'Primary', if (Type = 'Blue', 'Primary', if(Type = 'Purple', 'Secondary', Null()))) as TypeFlag;

Regards,

H

View solution in original post

vishsaggi
Champion III
Champion III

Can you use this?

LOAD *, Pick(Match(Type, 'Red', 'Blue', 'Purple'), 'Primary', 'Primary', 'Secondary') AS Flag INLINE [

Item  ,Type

Shirt ,Red

Shirt ,Blue

Socks ,Red

Shoes ,Green

Tie   ,Purple

Tie   ,Red

Socks ,Yellow

];

Dim: Flag

Expr: Count(Item)

hector_munoz
Specialist
Specialist

Also, a good solution is creating a MAPPING TABLE:

T0:

MAPPING LOAD * INLINE [

Type, TypeFlag

Red,Primary

Blue,Primary

Purple,Secondary

];

T1:

LOAD *,

  If(Type = 'Red', 'Primary', if (Type = 'Blue', 'Primary', if(Type = 'Purple', 'Secondary', Null()))) as TypeFlag,

  ApplyMap('T0', Type, Null()) AS TypeFlag2;

LOAD * INLINE [

Item,Type

Shirt,Red

Shirt,Blue

Socks,Red

Shoes,Green

Tie,Purple

Tie,Red

Socks,Yellow

];

, in which you associate flags to values and later you use a funtion ApplyMap() similar to lookup to recover the flag.

Anyway, you have to supress null values in objects if you donot want them to appear.

Regards,

H