Help in transforming an excel formula into Alteryx workflow

[kunalkhanna1132]

Hey guys!

I would like your help in automating this excel formula GG2=IF(D2="","",+IF(ROW()=MATCH(G2,G:G,0),1,0)) in Alteryx.

This is the workflow I got after using AI but I don't have the data to check it. Would really like any input on the correctness of this workflow.

1. Input Data:

   • Use an Input Data tool to bring in your Excel data. Ensure that columns D and G are included in the dataset.

2. Filter Tool:

   • Use a Filter tool to exclude rows where D2 is empty. This corresponds to the first part of the Excel formula.
   • Filter Expression: [D] != ""

3. Sort Tool:

   • Use a Sort tool to sort your data by column G. This will help in identifying the first occurrence of each value.
   • Sort Field: G

4. Record ID Tool:

   • Use a Record ID tool to add a unique identifier to each row. This will help in tracking the original row numbers.

5. Find First Occurrence:

   • Use a Summarize tool to find the minimum Record ID for each unique value in column G. This will identify the first occurrence of each value.
   • Group By Field: G
   • Action: Minimum on the Record ID field

6. Join Tool:

   • Use a Join tool to merge the original dataset with the summarized data. This will allow you to mark the first occurrence in the original dataset.
   • Join Field: G

7. Formula Tool:

   • Add a Formula tool to create a new column that replicates the logic of the Excel formula.
   • Formula Logic:IF ISNULL([D]) OR ISEMPTY([D]) THEN ""
     ELSEIF [RecordID] = [MinRecordID] THEN 1 ELSE 0 ENDIF
   • Explanation:
     • The formula checks if D is null or empty, returning an empty string if true.
     • It then checks if the current row's Record ID matches the minimum Record ID for that value in G, returning 1 if true, and 0 otherwise.

[abacon]

@kunalkhanna1132 Do you have the excel file that the formula is in? I have found AI give decent answers but there may be easier ways to do what it is saying to do. However, it does seem that the prescribed workflow from AI would work on the formula you gave.

[kunalkhanna1132]

@abacon Thanks for the reply, bro. I do have the excel file but I am not at liberty to share it.

As long as it works, it's fine.

Appreciate the help, bro☺️!

[abacon]

@kunalkhanna1132 I totally understand. If you can post dummy data with the formula I can try to recreate in alteryx so you have other options.

[kunalkhanna1132]

Okay bro. Will try to do that.