Loop Data by N Instances. Join Data by Specific Criteria and then Divide the Total

[Justin53Q]

Hi 😀

I'm trying to figure out how one would create a workflow to do something like the attached. But unfortunately, I've not been using Alteryx for a year or two and could do with some assistance or pointers with how to go about solving it 🤖

I've got 2 data sets:

• Diary
• Fruit Schedule

What I would like to do, is to take the Diary data set and repeat these data based on the number of items (i.e. 3) within the Fruit Schedule.  Then in the new Diary data set, the Item should have 1,2,3 to suit the match the item number within the Fruit Schedule. 

Then I'd like to take the item information and join it to the relevant corresponding item number, but only for Relevant "Yes" instances in the new Diary data set. 

Finally, where the Date in the new extended Diary is "Yes", take the Total from the Fruit Schedule and divide it by the number of Yes's in the new Diary data set.

Hopefully this makes sense.

I've also attached the desired output.

I would like to see how you would go creating a workflow to suit. 

Would love to do it myself but not at this level yet!

Thank you so much 

Justin

[DanM]

@Justin53Q 

To duplicate the Diary set, you can use a Formula tool and create a new field named "Item" and in the text field just add the number 1 and make the data type a double. If you are not looking to make this dynamic you could then repeat the Formula tool process three more times replacing the 1 with a 2 and 3. Then take the three data streams and use the Union Tool to put them on top of each other. That would give you the final piece for the diary portion of the final output.

Then in the same workflow add the fruit schedule file and add a Formula tool to create a new field named "Relevant" and in the text box use "Yes" and make it a string data type. From there you will want to use a generate rows tool to expand your date fields so that you have every date listed in between the Date from to the Date to (see the one tool example for the generate rows tool under Help or the tool mastery article to get all your dates.

Once you have all your dates that match what's on your diary data, you can then do a join between the two data sets which you will join on item, date, and relevant to get your final output.

If you need something more dynamic than this solution a iterative macro may be of use using a similar concept above.

Dan

[Qiu · Accepted solution]

@Justin53Q 
I have prepared a sample workflow as attached.
Basically it is about DateTime Format, Join and some conditional Statement and I believe there are 2 typos in your Excel data, which took me sometime to realize it. 😁

[Justin53Q]

Thank you so much for your time on this Dan.  I'm working on improving my data skills and will try building this out.  I'm using the online videos to get up to speed.

[Justin53Q]

Thank you so much Qlu for taking the time to do this.  I'm going to follow along with the steps and learn.  It is really great to see how you approached this.

All the best and massive thank you again.  NB sorry about the spelling issue 

Justin 

[Qiu]

@Justin53Q 
Happy to help. 😀

[Justin53Q]

Hi @Qiu 

Thank you so much again for the solution.  With regard to the formula tool and formula below.  I wonder if you get a few minutes spare, could you assist mw with a slight variation to the formula. 

This is the current formula; thank you

if [Relevant ] = "Yes" and [Source_Date to_Out] >= [Date from_Out] and [Source_Date to_Out] <= [Date to_Out] then [Description] else Null() endif

What I'd like to calculate is if [Relevant] = "Yes" this (in my mind) = full day or 100%.  But now I also need to consider in my calculation a few instances where [Relevant] days = "Yes", but these days are only half days or 50%; and thus, the cost in these instances would be 50% of a full day.  Thus, is it easier to change [Relevant] data from "Yes" in the data set to 100% for full days, and 50% for any half days?  And then adjust the formula. 

If it is easier to change the [Relevant] data from "Yes" to 100 and 50%, I would probably get stuck doing to formula but would be interested to see any solution.

Any assistance would be appreciated, and I know it's a big ask, so please feel free to say no if I'm asking too much.

Hope it makes sense

Thank you

Justin