Is value exists before ?

[Benji27]

Hello everyone

I need your help : I try to make a flag column with Multirow formula tool that says is value aleady appears on a previous date

For example :

the MissionID IG0001 is both on line of 2024-05-31 and 2023-11-30 so the flag = YES

the MissionID IG0002 is only on line of 2024-05-31 so the flag = NO

the MissionID IG0003 is only on line of 2024-05-31 so the flag = NO

Date	RecoID	MissionID	Flag
2023-11-30	RR1111	IG0001	YES
2024-05-31	RR1111	IG0001	YES
2023-11-30	RR5555	IG0001	YES
2024-05-31	RR5555	IG0001	YES
2024-05-31	RR6666	IG0002	NO
2024-05-31	RR7777	IG0002	NO
2024-05-31	RR8888	IG0003	NO
2024-05-31	RR9999	IG0003	NO
2024-05-31	RR1000	IG0003	NO

Can you kindly tell me if it's possible on Alteryx with Multirow formula tool ?

Thanks a lot guys :)

[cjaneczko · Accepted solution]

You can use a Summarize Tool and then any instance greater than 1 is a YES otherwise its NO. 

[Qiu]

@Benji27 
Just trying something different with Multi-row formula tool, though I agree the flow from @cjaneczko is more elegant.

[leefarrell]

1. Sort the Data: First, sort your data by MissionID and then by Date.

2. Use Multirow Formula Tool: Add the Multirow Formula tool and create a new column for the flag.

3. Set Up the Formula:

   • In the Multirow Formula tool, select the MissionID column.
   • Write a formula to compare the current MissionID with the previous row's MissionID.
   • If the MissionID is the same as any previous row, set the flag to "YES"; otherwise, set it to "NO".

Here’s a basic formula you can use in the Multirow tool:

IF [Row-1:MissionID] = [MissionID] THEN "YES" ELSE "NO" ENDIF

This checks if the MissionID in the current row matches the previous row. You may need to adjust the logic slightly based on your exact data setup, but this should give you a good starting point.

[Benji27]

Thank you all for your kindly reply !

So, it seems to be not possible to do it without Join or Summarize tool :(

[CoG]

You do well to seek after the path of the Multi Row Formula Tool. Your dedication to the Preparation Tools alone is very commendable. ;)

You can do it if you are allowed to use 2 Multi Row Formula Tools. Depending on the exact requirements of the problem, you might be able to get away with using just 1, but that doesn't seem to be the case due to the RecoID field not also being involved in distinguishing between records.

Happy Solving!