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

[Anonymous]

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?

[hector_munoz · Accepted solution]

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

[vishsaggi]

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]

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