Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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)
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