Use Multi Row Formula to Find Current Location of Shipment

[andreahq]

Hi,

I'm trying to find the current location of a Shipment. I used the transpose tool to get my data into rows and now I'm trying to use the multirow formula to find the Plant where the shipment currently is. However, I'm not sure if this is the best approach.

Input (after transpose):

Desired Output:

This is what the original source looked before transposing for your reference:

[Kenda]

Hey @andreahq 

I think I've accomplished what you're looking for with a couple Sample tools. First off, great job thinking of transposing it. I would've done the same thing!

So it look like you're wanting to keep the second to last record for each RecordID?

If this is the case, first use a filter to get rid of any rows where the Value field is empty. From there, use one Sample tool to group by RecordID then keep only the last 2 rows. Finally, use another Sample tool to again group by RecordID but this time only keep the first row.

First Sample toolSecond Sample toolWorkflow

[echuong1]

You can assign the max node number to each set of values. From there, you can compare the node number (parsed from the description) to the max number. If they are equal, they will be filtered out of T. The remainder will come out of F.

[andreahq]

Hi Kenda,

Thanks for your response. I actually want to keep the record based on the "Max Node" value. For example for the Record ID 1, I want the 3rd item, but for the record ID 2, I wanted the 4th item.

Thank you,

[atcodedog05]

Hi @andreahq 

Can you share this data as excel

We will be able to help you better

[andreahq]

Hi echuong,

I think this is what I'm looking for but when I run it in my workflow I get all zeros. Not sure what I'm doing wrong

[atcodedog05]

Hi @andreahq 

Can you please share the data in a file before transpose tool.

I can surely help you out 🙂 I kind of have an idea what to do.

Its really hard to work with a image

[echuong1]

Would you happen to have spaces in the Name field? Can you try copy and pasting "Max Node Number" from the name field into the formula?

Are you able to include a copy of your workflow? The general syntax is correct.

[andreahq]

@atcodedog05 Here is a sample of the full data

[echuong1]

See attached. You actually don't need the multi-row if this is the format your data is in.