Data loading with a condition on date

[Nourrahmcnean]

Hello world,

I want to load a table for a certain period, I chose April for this example. Since I'm not sure that each date exists, I created a condition to check that.

Let vJ_D=DATE#('01/04/2021','DD/MM/YYYY');
Let vJ_F=DATE#('30/04/2021','DD/MM/YYYY');
Let vNbr_Jour=floor(vJ_F - vJ_D)+1;
For j = 1 to vNbr_Jour
NoConcatenate
TABLE:
Load DATE#(Date,'DD/MM/YYYY') as Date
, brand , criteria , assesement 
Resident TAB
Where match(DATE#(Date,'DD/MM/YYYY') , $(vJ_D)+$(j)-1) //
next j;

Unfortunately, the loop for isn't executed. I think this expression $(vJ_D)+$(j)-1 is wrong.How should I change that expression?

Thanks in advance

[Or]

This doesn't exactly answer your question, but - why aren't you just loading values that are >= vJ_D and <= vJ_F? That would be both easier and faster than using a loop, generally.

[Nourrahmcnean]

Because I want to load it for each date then store it in a qvd file. I want to have a qvd file for each date

[Or · Accepted solution]

I see. I suppose that means you do need a loop here...

Something along the lines of e.g. (Note: This isn't precise syntax but it should give you the right idea):

Let vStart = today()-30;

Let vEnd = Today();

Do while vStart <= vEnd

YourTable:

Load Field

Resident Table1

Where DateField = '$(vStart)';

Store YourTable into LocationFile;

Drop Table YourTable;

Let vStart = vStart + 1;

Loop

[Nourrahmcnean]

Thanks. Today() gives you the date of the executing the script right?

[Or]

Indeed - I just used that as arbitrary values, though. You can place any two dates you want in the start and end variables.

[Nourrahmcnean]

Thank you it worked. It's really weird that loop for doesn't do the job.

[Or]

I'm sure your loop could have been adjusted to work, but I have to admit I found it easier to write my own from scratch than to troubleshoot an existing loop. Normally looping through values is easier with Load...While, but in this case since you're storing individual QVD files that isn't an option either.