IIf(tblDetailJournal!Account Like "10*",Null,tblDetailJournal!Region) AS Region

[diva]

How do we put this statement IIf(tblDetailJournal!Account Like "10*",Null,tblDetailJournal!Region) AS Region in alteryx ??/

[danrh]

The easiest way would probably be something like this in a Formula tool:

IIF(Left([tblDetailJournal!Account],2)="10", Null(), [tblDetailJournal!Region])

[diva]

sorry, its not working here I need the details of the account that start with 10 and the solution you have given is giving null records

[danrh · Accepted solution]

It looks like the original formula you gave is from Access and will produce a column named Region that has a null for all fields where the Account starts with "10" and will give the Region for all the other rows.  Are you looking to null out all Regions where the Account starts with "10", or are you looking to filter down to records where the Account starts with "10"?

The provided formula will null out the Region for "10" records..  In your attached screenshot, it looks like you've changed Null() to IsNull(0).  Null() produces a null value.  IsNull(0) checks whether the constant "0" is null (which it never will be) and returns a boolean, true or false.

If you're looking to filter down to just the "10" account records, try a Filter tool.  Choose "Custom Filter" and enter

Left([Account],2)="10"

as the expression.

If I'm completely missing the boat on this one, let me know.  If so, some sample data and your expected output would be helpful.  Hope it helps!

[diva]

thank you for the solution

[danrh]

You bet, glad it worked!