cancel
Showing results for 
Search instead for 
Did you mean: 

Macro Get 2nd Value from different tab on same data source

SOLVED
Highlighted
aehrenwo
Asteroid

I have a working batch macro that is processing fine that is pulling data from the first tab of multiple excel files and going through a number of other steps. 

 

I need to filter the one of the datasets during the workflow based on one particular cell in the 2nd tab on the same source files being processed through. 

 

How do I set this up so that both are iterating simultaneously? Is it just another configuration tool? I figure this should be simple but can figure out how to get that value to be stored a variable to do the filter .

 

Thanks ,


Adam

Magnetar
Magnetar

Hey @aehrenwo

This sounds like a really interesting challenge - are you able to put the beginnings of your work into a workflow so that we can get the community to help solve this ( much the same as @JoeM structures the weekly challenges here https://community.alteryx.com/t5/Weekly-Challenge/bd-p/weeklychallenge)

 

If you can mock up the skeleton, and some sample data, and wrap this up to attach to this thread, then we can all work together to help you solve this.

 

:-) we have an entire weekend to help!

 

 

aehrenwo
Asteroid

I would love to mock this up. However, one of the source data points is our entire HR hierarchy for the company (200k+ users). The main point of this workflow is to marry unique identified numbers that the team wants reporting on against the hierarchy and then again against training file detail. 

 

I think if i mocked this up on a small scale it would be hard to replicate all of the scenarios.  I will see if I can come up with a version of 100 records or something from each file that is fake to facilitate this....

 

For this exercise do you only care about the main workflow that is processing the files? I am using the Conditional Runner Macro as well to run other processed (i.e prepare templates in a report folder for the final data to reside ,etc.) Should that all be included - and you can figure out how it all connects together? 

 

would really appreciate help on this. ... I will start working on same fake data. 

 

Adam

Magnetar
Magnetar

:-) don't worry about the up-stream flows - try to isolate the piece that you're struggling with.

 

The best way to think about this is just creating a sample of what the data-set would look like at the point that you're struggling - do it in Excel to make it quick, and then import it into a Text Input tool on the alteryx canvas.

You can do the same with the expected output.

 

Have a look at this particular HR challenge - the goal here is to create an HR hierarchy, so you may be able to steal the starting point from this flow to mock up yours (and possibly some of the solutions will also apply).

 

https://community.alteryx.com/t5/Weekly-Challenge/Challenge-12-Creating-an-HR-Hierarchy/m-p/36740

 

Looking forward to seeing what you come up with

Sean

aehrenwo
Asteroid

Sean-


As request I have created a MockUp for the Workflow that I am trying to include the additional filter control.  Even though it includes control parameters as part of the batch processing I am assuming you can ignore those to work on the enhancement I am looking for...

 

The basic concept of this workflow: 

  1. Parse through a config file that includes lists of leaders that team wants training data on. 
  2. Join that with existing LMS data that includes assignments and their current status. 
  3. Join that further with Hierarchy level data to determine who reports to who to isolate who needs to be in the final output (under which grouping). 

for this mockup I have limited the LMS data to only 1000 unique records and only 4 leaders that all 1000 report to... so VERY basic.  Normally, our hierarchy file can have up to 15 levels populated. I have simplified this relationship and only have Level 1 populated.  

 

The modification I am looking to implement is to have the workflow also part the 2nd tab (SLR_Course_Owner)of the "template" config file to look at Cell B3 and get the Course Code...  and then filter the final results accordingly based on that code... 

 

I have be struggling to parse the same file in the batch process for both pieces of data.  Let me know if anything is unclear. 

 

Thanks,

 

Adam

Magnetar
Magnetar

Hey Adam,

I've created a mockup in the format that @JoeM uses for the weekly exercises - this helps to make sure that we can isolate the problem, remove any other complexities, and focus on the issue you're trying to solve.    This will also make it really easy for other folks in the community to have a crack (by way of an example - weekly challenges that Joe posts in this format are generally solved within an hour or two of being posted, and within a week have 5-10 different solutions).

 

I've also taken your notes and captured them in the comment box titled "Challenge" - feel free to amend this if I've got it wrong.

Note: To make this kind of thing easier to distribute, I generally pull excel inputs directly into a text input control (so that they ship very easily with the flow without having to do an export)

 

have a look and see if I've got some of this wrong.    In the meanwhile I'll start trying to pull together a solution.

BTW - I didn't understand the need around walking the HR Heirarchy - not sure if you've cracked this in your working version, but if you mock up a few rows of HR data with all the required data, I'm happy to work through this part too.

Magnetar
Magnetar

Hey Adam,

 

Not sure if I've hit the mark, but essentially what I've done is used a dynamic input to read the course name out of the template files (referred to in the Team LeaderInput) - and then use this to filter the "Training Course Code" from the Assignment data

 

Unfortunately I can't attach the packaged workflow to this reply - but if you PM me your e-mail address I'll send it over - in the interim I've

 attached the base workflow and you should be able to see how this works.

 

Let me know?

 

BTW - if you struggle to open this because of the version of Alteryx - there's a quick workaround here:

https://community.alteryx.com/t5/Data-Preparation-Blending/Subtracting-from-result-in-Multi-Formula-...

 

If this helps - could you mark this as "Solved" - or if there are still open questions, just reply with an updated workflow detail attached.

 

Cheers Adam

Sean

 

aehrenwo
Asteroid

Thank you so much... I was able to adapt this to my current workflow and it worked perfectly. was actually a lot less involved than I thought.