Load Where Variable Loop

[GaryMcDonald]

Hello,

I'm unsure how to best put this into practice any advice/help greatly appreciated.

I have the below script and I currently replicate it for every BatchNumber that I have (this increase month on month), I then also need to replicate it for 6 filters (these are [CL Filter 1k],[CL Filter 2k],[CL Filter 3k], etc . 6 in total).

I assume there is a way to create a variable that allows the script to loop for each of these? Any idea where to start?

Many thanks

Left Join (QuoteResults)
Load
UUID_All,
AutoNumber(Rowno(),[Quote Number]&'A')as [CTM Rank Lt 1k]
Resident QuoteResults
Where BatchNumber = 1 and [CL Filter 1k] =1
Order by total_price asc;

Replicate:

Note:

[CL Filter 1k] is a field that acts as a flag 1=yes,0=no there are 6 filters (1k, 2k, 3k, 4k, 5k, 10k)

(BatchNumbers increase month on month on moth currently 1-6) 

I have been increasing the number of tables each month so I now have 36 tables increase by 6 every month.

Left Join (QuoteResults)
Load
UUID_All,
AutoNumber(Rowno(),[Quote Number]&'A')as [CTM Rank Lt 1k]
Resident QuoteResults
Where BatchNumber = 2 and [CL Filter 1k] =1
Order by total_price asc;

Left Join (QuoteResults)
Load
UUID_All,
AutoNumber(Rowno(),[Quote Number]&'A')as [CTM Rank Lt 1k]
Resident QuoteResults
Where BatchNumber = 1 and [CL Filter 2k] =1
Order by total_price asc;

[Thiago_Justen_]

Hi there.

What about this?

First, let's get the Max BatchNumber from QuoteResults.

Max_BatchNumber:

Load

      Max(BatchNumber) As Max_BatchNumber 

Resident QuoteResults;

Let vMaxBatchNumber = Peek('Max_BatchNumber');

Drop Table Max_BatchNumber;

Then, let's perform a For..Next loop from 1 to Max_BatchNumber.

For vBatchNumber=1 to $(Max_BatchNumber)

          Temp_Batch:

           Load
                     UUID_All,
                     AutoNumber(Rowno(),[Quote Number]&'A')as [CTM Rank Lt 1k]
         Resident QuoteResults
                     Where BatchNumber = $(vBatchNumber) and [CL Filter $(vBatchNumber)k] =$(vBatchNumber);   

Next

Finally, we can join Temp_Batch with QuoteResults. 

I hope it helps you.

Regards,