Comparing multiple rows with same values

[iamRandy]

Hi,

I have a set of data where OrderNumber column can have duplicate values and order status for the duplicate order number can have a Cancel or Approve status.  I need to know how to compare the order number value by using a formula to mark the duplicate order number as 1 order number. 

For example, Order #1 on 12/1/2022 with a status of "Approved" then on 12/3/2022 Order #1 was "Cancelled" . I need to assign a status "False" on the Cancelled order.  

Is this possible through formula?

[PanPP]

Hi @iamRandy 

Would you be able to show a sample output?

You can use a conditional statement described below: If [Status] = "Cancelled" then "False" else [Status] endif

You can then summarize and group by order numbers based on status.

Hope this helps, if it does please like this post and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.

[binuacs]

@iamRandy Are you looking for something like the below?

[Adrian_T]

Hey @iamRandy, I believe the solutions provided by @PanPP and @binuacs will work perfectly for you. I thought to share the following resource on Conditional Functions in Alteryx.

In your use case, you are looking at the IF THEN ELSE conditional statement:

IF c THEN t ELSE f ENDIF
Returns t if the condition c is true, else returns f.

[iamRandy]

I need to compare if the ORder ID is the same. IF it is the same it should be false.

[Adrian_T]

Hey @iamRandy,

You are looking at the Multi-Row Formula Tool here. I have attached a sample workflow which will achieve this.

Assuming you would like to assign the earliest date for each order ID as "True" and subsequent dates "False", we first sort the data before applying the conditional statement using the Multi-Row Formula Tool.

[iamRandy]

Thanks. I will try to apply this method.