summarize - total quantity

[ivoiculescu2020]

Hello everyone,

I try to create a report that will be automatically sent to Supply Coordinators and Distribution Coordinators (if SC could not be found in the system) and I need your help with 3 aspects:

1. the report that will be sent out will present the lot total quantity per lot/per depot. I already use Summarize tool and grouped by Lot and Depot, I do get the total quantity; however, the output report I need it to also show some of the initial columns: description, expiry date, SC, DC and decision. How do I bring them in after I used summarize? Do I use Join? I am not entirely sure though...

I attached a sample of an input file

2. How do I ask Alteryx to create a dropdown menu in the final column: Decision column? I checked the community/learning paths and it seems I need to download a legacy excel drive but I need admin rights for that to happen and I don't. Is there another function that I can use to make this happen? The option in the dropdown menu would be:

Keep

Destroy

Partial Destroy

Wait for the expiry date

3. I already configured the email to send it to the SCs or DCs (when no SC is found). However, the email will say something like

"you receive this because you are currently listed as the SC for depot XYZ in the system. If you're not the correct SC for this depot, please login to the system and make the required changes"

How do I ask Alteryx to fill in automatically XYZ with the depot name appearing on the report?

For example, if the email is sent to John Deniro, I need the depot to say "...depot NYC" (so replace XYZ with the actual depot title)

Or if the email goes to Ava Max, then the email should state "...depot Berlin"

Any ideas on how I can accomplish this?

Thank you!

Kind regards,

Ioana

[cjaneczko]

For the first step you would use a union tool to union the Subtotals in with the detail. Then put a sort tool after it so the subtotals will be sorted  with the detail. For step 2 its not possible to create an output validation drop down menu to excel. That would need to be done in excel after the fact. The drop down tool thats in Alteryx is as an input mechanism in Apps and passing values through the workflow. its not used as an output method.

As for step 3 I don't have the information to help you with that part. I haven't used the email function yet. But I would think you would use a formula tool in a new field that has some concatenation as follows. [Contact Field] would be the the field in your workflow that has the Depot information. 

"you receive this because you are currently listed as the SC for depot " + [Contact Field] + " in the system. If you're not the correct SC for this depot, please login to the system and make the required changes"

[rzdodson]

@ivoiculescu2020 appended below are some answers/guidance around the questions you are asking.

1. How do I bring them in after I used summarize? Do I use Join?: What I would personally recommend is first using a RecordID tool (Preparation palette), and then bringing that field in to your Summarize tool. Typically, the RecordID field will default to a Int32 field - change this to a string field within that tool. This will allow you to bring that field in without the warning message from attempting to group on a numeric field.

After these steps are completed, then I would join on the RecordID field that now exists within both tables. This Join will also enable you to bring in qualitative fields (description, SC, DC, decision, etc.

2. How do I ask Alteryx to create a dropdown menu in the final column: Decision column?: It sounds like you are verging on Alteryx Analytic Application territory here, giving users the ability to select from a list of pre-determined/standard actions to take for SC->DC or DC->SC distribution. I would create a list of those pre-determined actions, and have it feed a Drop-Down tool. You can then use the Drop-Down tool to "render" a decision in that Decision column you created. (Will attach a workflow soon).

3.  For example, if the email is sent to John Deniro, I need the depot to say "...depot NYC" (so replace XYZ with the actual depot title)

Or if the email goes to Ava Max, then the email should state "...depot Berlin". Any ideas on how I can accomplish this? If this field is different than your Distribution or Supply coordinator in your data set, I would recommend creating some type of POC field that associates the warehouse to the POC that needs to be emailed. This may require two additional fields within your data set. After this is done, you can feed that field in to your Email tool dynamically. Below is an example using your Distribution coordinator field as a test case.

[ivoiculescu2020]

@rzdodson 

option 1 worked, thank you so much!

option 2: I am not sure it's going to work because I plan to schedule this report to automatically run twice a month. So I don't think an analytic app could be scheduled to do this? what do you reckon?

option 3: can you give me a few more details about your solution? As I am still unsure how to apply it so that Alteryx knows to replace XYZ with the appropriate SC or DC name. Should I add a group by depot in the summarize field before sending out the email?

thank you for your prompt help!