Loop query based on each row

[HeadBanger]

Good day,

I'm trying to query a database to look up SKU metrics during a time period that I'm targeting for each SKU.  

The problem we have is that there are 8000 different SKU / Date Range combinations.  

I'm thinking a batch macro might solve this but i'm only guessing.  Not even sure where to begin?  

Let me know if anyone has ever looked worked on something similar that could solve this challenge

Thanks for your time.

Sam

[mbarone]

Do you have a mock-up of what the data will look like going into the database?  Is it just a list of SKUs and the corresponding start/end date/time? What would the output look like (again, a mock up)?  A batch macro might indeed be the way to go.

[HeadBanger]

Hi mbarone,

Here is an attempted mock-up.  Ideally it would be great if the query could be updated using a loop, updating sales for each SKU based on date range provided by the “Effective_date” and “End_Date” columns.

Hope this helps clear up my communication

[mbarone · Accepted solution]

I think the attached should help.  If I'm understanding correctly, you can use a batch macro.  See attached.  Once you have a complete list of the sku-startdate-enddate combinations, you feed that into your macro.  Your macro runs one iteration for each record feeding into it, meaning one run for each sku-startdate-enddate combination.  It will union (stack) all output from each run.

The macro itself has your query with single hardcoded values for a specific sku-startdate-enddate combination.  

Take a look at the attached and that should give you a start.

[HeadBanger]

Hi mbarone,

Thanks for sending this.  

Just looking at the file you shared YXMD2.yxmd.  I cant see what tool you are using there only shows a question mark?

Hopefully just a dumb question and not an additional feature i would have to purchase 😜

[mbarone]

Not a dumb question, but no purchase required 🙂

You have to download YXMC2 too.  Save it wherever, delete the question mark tool, and right click the canvas to "insert - macro" and navigate to YXMC2.  Then configure the macro as indicated.  Then right click the macro tool and click the black + symbol to see the guts of the macro (or alternatively, you can drag YXMC2 from your desktop or wherever to a blank canvas - that also shows you the guts of it.

[HeadBanger]

I think this worked!  Which would be super exciting.

I cant tell you yet since its running thru about 8000 SKUs with 180 different date ranges, so by my estimates would be about 1.4 M iterations.

I might have an update tomorrow if the database doesn't crash.

😜

[mbarone]

Excellent, I'm curious now let me know LOL !!

[HeadBanger]

UPDATE!

I stopped it after about 2 hrs of it running last night and do see that it is working properly.  pulling each SKU for each of the date ranges.

So exciting but after running overnight and checking this morning it was still running, it got about half way thru and said my query doesn't have the reservation in the data region which I assume means it reached the data limit allowed.  

Gonna try splitting it up in 3 parts and perhaps it will work a little faster and not bog down the resources.

Thanks for the big help mbarone!  My boss didn't think this was possible so its good to show the power of this incredible platform.

[mbarone]

Great to hear, glad to help!