Multi Field & Multi Row Logic

[suby]

Hi All,

I have a Output Dataset like these ( Attached the test Data)

I'm looking for a solution where i want to create a new flag with 0 and 1

Logic

CUSOTMER NAME = IF it has got a Value of Masked then the Flag column should have 1 against it.

PRODUCT ID = IF it has got negative values like (-9,-12) then the Flag column should have 1 against it.

Is this possible any help would be much appreciated.

Thanks

[binuacs · Accepted solution]

@suby one way of doing this with the formula tool

[IraWatt · Accepted solution]

Hey @suby,

I think what you are looking for is a formula like this:

IF Contains([CUSTOMER NAME ],"Masked") OR Contains([PRODUCT ID],"-") THEN 1 ELSE 0 ENDIF

The contains function looks for masked or the minus sign in the two columns referenced in the square brackets. If ether are true then it flags 1 else 0.

Great community videos on formulas here Interactive Lessons - Alteryx Community

Any questions or issues please ask :)
HTH!
Ira

[DataNath · Accepted solution]

How does this look @suby? As there's punctuation other than - in your [Product ID] field, Alteryx will read this as text and we can therefore just apply a couple of String expressions for the two fields in question. This seems to achieve the same as your desired output but let me know if there's any issues, especially if you test it against a wider dataset:

[Emmanuel_G · Accepted solution]

Hi @suby ,

Do you want something like this ?

[suby]

Thank you all for the quick response just to add in real world i have 50sheets and each sheet has more than 15 columns to check for is there a easy way of doing this rather than hard coding the column names.

[binuacs]

@suby @Can you provide sample input file and expected output ?

[Emmanuel_G · Accepted solution]

@suby 

Would it be possible to share an example?

But just like that, if you have several columns, with a transpose tool I think that should do it.

It will suffice to indicate the conditions in a formula or filter tool so that they apply to the rows so no need to name each column.

Find an example in attachment with your previous sample.

[suby]

Hi Emmanuel G,

Thanks for your solution it works just to clarify one more quick thing.

Say in my new data set attached i have a another column 'No of Days to Reporting Month' which holds the value of -10

When i transpose the Data using your method it includes this 'No of Days to Reporting Month' and the Flag is set to 1 where i don't want this to happen.

what I'm looking for is i  want to use your existing  solution provided and on top of it i  don't' want the Flag to be set to 1 for the  'No of Days to Reporting Month' column also i don't want any filters by hard coding to exclude this column since I'm using your solution on a  batch macro where your solution runs on multiple sheets so is there a dynamic way of doing this without hard coding.

Attached the New Data set.