Filter with formulas

[beatrizmguerreiro]

Hello, 

I have a group of condtions that have to be met. In the end, I only want to have the results with "True" value. 

I am using the FILTER tool. E.g.: 

ISNULL([Field 1])

OR

ISNULL([Field 2])

OR

ISNULL([Field 3])

However, there also conditions as follows:

- Validate if the month in date (Column name: Date 1; format: DD/MM/YYYY) is matching the Month (Column name: Date 2; format: MM)

- Validate if the ISO country code (Column name: ISO) matches the Country name (Country).

Can also have this in the FILTER tool- That way, in the end, I can only retrieve the True values. Or is it required to have a FORMULA tool? 

[marcusblackhill]

Hey @beatrizmguerreiro !

Looks like something you can check inside the filter tool as well. But to understand better and provide something solid to you, can share a example data? can be only 2 or 3 lines, its enough.

[beatrizmguerreiro]

Hope this helps

Thank you for your help.

[marcusblackhill · Accepted solution]

Hey @beatrizmguerreiro !

thanks for the database, the only problem for me is the ISO/Country check because they have different patterns to compare, but for the rest you can use the formula below inside the filter

IsNull([Field1]) or IsNull([Field2]) or IsNull([Field 3]) and (substring([Date1],3,2) = PadLeft(tostring([Date2]), 2, "0"))

To solve you problem of ISO/Country, maybe will be better to create a side table maybe with text input with all associations of country and ISO to input that inside the table with a find/replace tool maybe or a join. Doing that you can just add to the formula a new check of ISO = new field.

Hope that helps!

[beatrizmguerreiro]

Hello Marcus, 

That last condition is not working properly 😕 . Can you help here?

If I use AND , this is not filtering correctly;

If I use OR it will filter the last condition, indeed, but ignore the reamining conditions. 

[marcusblackhill]

Maybe I understood wrong some part. Can explain again which case need to appear in the filter?

The current idea of the formula is to get if is null in field 1 or field 2 or field 3 and if the month check retrieve true, so, at least one of the "fields" need to be null and at the same time the month check be true. Is that you need?

[beatrizmguerreiro]

Sorry, no it was my fault.

In my ouput file, I only want to filter values that are FALSE in "Fields", cause those won't be empty.

Regarding the date situation: I want to check if the month in Date1 (31/01/2020) = month in Date2 (represented by a number - Jan = 1; Feb = 2 ...). If YES, add to the output file, ELSE don't. 

[marcusblackhill · Accepted solution]

So, actually your output need to be a table with all "fields" not null and with the month check true, right?

If is that, try the formula below:

!IsNull([Field1]) and !IsNull([Field2]) and !IsNull([Field 3]) and (substring([Date1],3,2) = PadLeft(tostring([Date2]), 2, "0"))

[beatrizmguerreiro]

By curiosity, what does this mean "!IsNull" ? 

[marcusblackhill]

Sure, actually for anything in alteryx, the "!" before something is a negation of the thing after, so in that case instead of I'm getting nulls I'm getting only what is not null.