"INDIRECT" in Alteryx

[YLYONG]

Hi All,

I have an excel worksheet where I used "INDIRECT" to obtain the results that I wanted.

Wanting to reach out the community if anyone knows or has any idea how  "INDIRECT" workflow works in Alteryx?

Thank you

[danilang]

Hi @YLYONG 

An Indirect reference in excel is a way to build up a reference to a specific cell in a worksheet and return the value in that cell.  Since Alteryx doesn't deal with absolute cell references the way the excel does, we need to find a way to translate the concept into something that Alteryx can do...A Join

Since a Join has to join on a specific column, we need to transpose the look up table so that all the values under the Day columns are in a single column with the Day names in a second column.  Then you join the input values to the transposed lookup on row and day, giving you

Dan

[YLYONG]

Thanks danilang for your input.

Still not getting the intended results...

The intended results of this workflow is for a store reference 5, 

if the running total is less than or equal to 123, return MON,

if the running total is between 123 and up to 247, return TUE,

etc.

[YLYONG]

I guess my question is how can we design a workflow / formula in Alteryx for INDIRECT embedded in IFS funtion?

[Elias_Nordlinder · Accepted solution]

Hello @YLYONG ,

I think the difference is that in the previous solution the input values is the Store Reference and the days, and the output is which running total that matches. 

You want as input values have the Store Reference and Quantity, and get the day as output.

You can do this by doing some small changes as the workflow attached shows:

1. Use the lookup table by @danilang, which is a great way to approach this problem!

2. Use the RecordID tool which will help later in the sample tool.

3. Change the input to have Store Reference and QTY instead of Store Reference and Day Name.

4. Join only in Store Name.

5. Filter out so that the QTY of the running total/from your file is always less than the QTY in the lookup Table.

6. Use the Sample Tool: By taking the first one for each recordID you will get the QTY that is closest to the Lookup Value (I.E only one day for each input value which will be correct).

7. Use the Select tool to only input the columns you want.

//Let me know if this works! 🙂
Regards

Elias

[YLYONG]

Hi @Elias_Nordlinder thank you for your input. It works!

However, I am still not understanding the logic behind this step:

5. Filter out so that the QTY of the running total/from your file is always less than the QTY in the lookup Table.

Would you mind explaining, thank you

[Elias_Nordlinder]

Great to hear @YLYONG 🙂

So basically it is the same thing as you mentioned before:

The intended results of this workflow is for a store reference 5, 
if the running total is less than or equal to 123, return MON,
if the running total is between 123 and up to 247, return TUE,
etc.

If we look at the first value before the filter it is 224.

If this filter would not be there the first LookupValue would be 123

The workflow would incorrectly say that this is a Monday as the first LookupValue is 123 -> Which is a Monday.
(As I afterwards pick the first lowest LookupValue for each Record.)

Without the filter:
Consequentially everyday would be Monday (Which actually is the Swedish title for Groundhog day, but that is maybe not relevant here)

With the filter it only look at QTY that are less than the LookupValue.

For store 5 all the Lookup Values below 224 are filtered out.

All the other Lookup Values are still there (247, 370, 494 ...)

I do afterwards use the sample tool to only take the Lookup Value that is the smallest

(Of the ones that are higher than the quantity).

In this case the Lookup Value would then be 247, which is Tuesday! 🙂

Hope this clear up the question 🙂

Don't forget to mark the solution as correct if it solves the question,

to help other people find it in the future

//Elias

[Elias_Nordlinder]

Hello @YLYONG ,

I will do a shorter post this time than my last that got deleted.

But if we look at how this would look without the filter, it would look like this:

All Days would be Monday.

As there is no filter on QTY < LookupValue,

the Lookup Value will automatically be the lowest one possible for each Store.

I use the sample tool afterwards to pick the lowest available LookupValue.

For this to work I need to set a filter that the Quantity needs to be

lower than the Lookup Value. Then it will look like this:

This is similar to your post where you wanted the running total to be below 123 and such, just

that it continues for all the stores and all LookupValues.

I hope that this helps.

Please mark my previous post as Accepted solution if it solves your problem 🙂

//Regards
Elias

[YLYONG]

@Elias_Nordlinder Thank you for your detailed explaination! This is a very good idea to prevent  a "groundhog day" scenario