topic Re: Data loading with a condition on date in App Development

Data loading with a condition on date

Nourrahmcnean — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: Data loading with a condition on date

Or — Thu, 25 Nov 2021 15:02:45 GMT

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.

Re: Data loading with a condition on date

Nourrahmcnean — Thu, 25 Nov 2021 15:04:38 GMT

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

Re: Data loading with a condition on date

Or — Thu, 25 Nov 2021 15:17:06 GMT

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

Re: Data loading with a condition on date

Nourrahmcnean — Thu, 25 Nov 2021 15:29:19 GMT

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

Re: Data loading with a condition on date

Or — Thu, 25 Nov 2021 15:50:54 GMT

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

Re: Data loading with a condition on date

Nourrahmcnean — Thu, 25 Nov 2021 16:08:10 GMT

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

Re: Data loading with a condition on date

Or — Thu, 25 Nov 2021 16:15:13 GMT

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.