Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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