Help on how to automate this formula using Alteryx

[kunalkhanna1132]

=IFERROR(SMALL(IF('Fact'!$G$2:$G$19607=1,'Fact'!$AB$2:$AB$10000),ROW()-2),"")

This formula is for a column and this is the excel formula breakdown:
The formula finds the nth smallest value in column AB where the corresponding value in column G is 1, and handles errors by returning an empty string if no such value exists. 

Please check if this formulas workflow is correct? I don't have the data to check its validity, so I would really like someone's help to know if it is correct. Or suggest changes or better solution if possible.

1. Input Data:

Use an Input Data tool to bring in your dataset from the "Fact" sheet, including columns G and AB.
2. Filter Tool:

Use a Filter Tool to apply the condition specified in the formula.
Filter Expression: [G] = 1
This filter will retain rows where column G is 1.
3. Sort Tool:

Use a Sort Tool to sort the filtered data by column AB in ascending order.
This step organizes the data to facilitate finding the nth smallest value.
4. Sample Tool:

Use a Sample Tool to select the nth row from the sorted data.
Configuration: Set the tool to skip the first n-1 rows and take the next row.
5. Formula Tool for Error Handling:

Add a Formula Tool to handle cases where the nth value does not exist.

Formula Logic:
IF ISNULL([AB]) THEN "" ELSE [AB] ENDIF

This formula assigns an empty string if the nth value is not found.

[Qiu]

@kunalkhanna1132 
Please kindly provide a sample set of date including input and desired output.

[kunalkhanna1132]

Hey @Qiu, Sorry! I thought you were replying to another query of mine. I don't have any dataset for this but I can describe the input. Column G should have either 0 or 1, Column AB should have string output like"0105114" or "-25447". I would be really thankful if you can tell me f this workflow would be good or any changes should be made.

[Raj]

@kunalkhanna1132it will be very difficult to understand the logic you are looking for
please provide some sample data 
will be able to help.

[Qiu]

@kunalkhanna1132 
I did search about the Excel function used, such as SMALL(), ROW() 😁
Kindly check this is the one you need.

[kunalkhanna1132]

Thanks @Qiu! I will check and get back to you regarding this. Once again thank you!

[kunalkhanna1132]

Hey @Raj I will try. Thanks!

[kunalkhanna1132]

Hey @Qiu Can you explain the logic behind the workflow if possible? Im finding it a little hard to understand it.

[Qiu · Accepted solution]

@kunalkhanna1132 
I add some notation but not much.
I believe if we fully understand the Excel function itself then the workflow is quite self-explained.

Below is what I got from the ChatGPT about the Excel Formula.

1. 'Fact'!$A$2:$A$19607=1
This checks if each value in the range A2:A19607 of the 'Fact' sheet is equal to 1.

The result is an array of TRUE or FALSE values of the same size.

2. IF('Fact'!$A$2:$A$19607=1,'Fact'!$B$2:$B$10000)
This is an array formula (typically entered with Ctrl + Shift + Enter in older versions of Excel).

For every row where the corresponding value in column A is 1, it returns the value in the same row from column B (B2:B10000).

If the value in column A ≠ 1, it returns FALSE or blank.

⚠️ Issue to note:
The range sizes don’t match exactly:

'Fact'!$A$2:$A$19607 is 19,606 rows.

'Fact'!$B$2:$B$10000 is 9,999 rows.
This can cause errors or unexpected behavior in array formulas. Ideally, both ranges should be the same length.

3. SMALL(..., ROW()-2)
The SMALL function returns the k-th smallest number from the array returned by the IF.

ROW()-2 gives a dynamic value depending on which row the formula is in.

If the formula is in row 3, ROW()-2 = 1 → returns the smallest.

If it's in row 4, ROW()-2 = 2 → returns the 2nd smallest, etc.

This effectively helps create a list of the smallest values matching the filter condition.

4. IFERROR(...,"")
If there’s an error (e.g. not enough matching values), it returns an empty string "" instead of showing an error.

[kunalkhanna1132]

Thanks @Qiu !