Condition for Filter

[Anonymous]

Hi

I need to display values in the filter as yes or no.

No - When the column CornerTool is "not run" or "test"

Yes - When the column CornetTool has anyvalues anything other than "not run" or "test"

Please Help...

[johnw · Accepted solution]

Best is to load it as a new field:

if(match(CornerTool,'not run','test'),'No','Yes') as NewField

But you can also enter that same expression directly into a list box (without the 'as NewField'). Just select <expression> instead of a field when you create the list box.

[MichaelRobertshaw]

In your Load Script, I recommend mapping the incoming data to your Yes/No values:

[Corner Map]:
MAPPING LOAD * INLINE [
Match, Replace
not run, No
test, No
];

[Product]:
LOAD field1
 ,field2
 ,ApplyMap('Corner Map', cornertool, 'Yes') as [Corner Tool]
 ,field4
 ;

SQL select field1, field2, cornertool, field4
 from schema.table

[#4]

Hi John

This format is exactly what I want to use however I cannot get it to work.

In my load statement I want to replace a yes/no field (ysnSHQSExemptTemp) that normally returns 1 and 0 with Yes or No

This expression returns all fields as "No"

if

(match(ysnSHQSExemptTemp,0,1),'No','Yes') as SHQS_T_Exempt

This expression returns all fields as "Yes"

if(match(ysnSHQSExemptTemp,'False','True'),'No','Yes') as SHQS_T_Exempt

Can you help please?

John

[johnw]

Sorry for the delay. I've barely had a spare moment all week. The match() function returns true if ANY of the items in the list match. So if all of your values are either 0 or 1, then you'll always get no for the first expression.

'False' and 'True' are string values, so won't match 0 or 1, so you always get 'Yes'. Replacing with false() and true() will work, but only by accident. What I mean by that is that true() is actually -1, but that means that a value of 1 will not match either false() or true(), and therefore go to your else, which is 'Yes'. So right results, but very poor logic.

I'd probaly just do this. Even though true() evaluates to -1, anything other than 0 will evaluate to true. So a value of 1 will be true and give you 'Yes', while a value of 0 will be false and give you 'No'.

if(ysnSHQSExemptTemp,'Yes','No') as SHQS_T_Exempt

Another approach that would be more similar to what you seem to be attempting is this:

pick(match(ysnSHQSExemptTemp,0,1),'No','Yes') as SHQS_T_Exempt

Match() will return the position of the first match. A value of 0 is in position 1. Pick() will return the value in the given position. The value of 'No' is in position 1. So you'll get 'No' for 0. Similarly, 1 is in position 2 in the match(), and position 2 is 'Yes' in the pick(). So you'll get 'Yes' for 1. To me it's more complicated than it needs to be, but it seemed worth mentioning.

[#6]

Hi john

Thanks for the solution and especially for explaining the logic behind it -

works 100%