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

Macros

In Database

Data Investigation

In-Database

Accepted answers

mbarone

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.

YXMD2.yxmd

YXMC2.yxmc

All comments

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

jamshed23

Dear mbarone ,

I have very big table, it's has more than 20 millions record, i need to fetch data, transfer and migrated to another database table. Load all at once and than migrate not possible. Does alteryx has FOR EACH LOOP like concept as it was SSIS ? something that split load

Workflow.jpg

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703