topic Re: Loop to add a date field multiple times question in App Development

Loop to add a date field multiple times question

alexis — Fri, 24 Sep 2021 20:59:44 GMT

Let us assume that I have a simple table called MyData 10 rows (say) and 2 fields:

MyData: // let's say I have 10 rows
Load MyField1, MyField2
From SomeTable;

and a MasterCalendar table that contains the fields:
CalDate, // date field (1/1/2020)
numCalDate // numeric representation of date (e.g. 43831)
for a period spanning say 1/1/2020 until today (appx 600 records) - I have the date start and end dates stored in variable vMinDate and vMaxDate in numeric format

I want to expand the MyData table so that for every one of the 10 records (say), we append a date field (let's call it MyDate) for every single date. In other words at the end of the exercise I would end up with a MyData file that will be 6000 records long (10 records x 600 different dates) - what is the most efficient way of doing this?

If it's easier, you can ignore the MasterCalendar and provide an approach using AutoGenerate to generate the dates with something like:

LOAD $(vMinDate) +RecNo() -1
AUTOGENERATE ($(vMaxDate) - $(vMinDate) + 1);

Thanks in advance

Re: Loop to add a date field multiple times question

marcus_sommer — Sat, 25 Sep 2021 08:53:21 GMT

You don't need a loop for it else you could just join the tables without a common key-field to get the cartesian product.

- Marcus

Re: Loop to add a date field multiple times question

Vegar — Sat, 25 Sep 2021 13:59:39 GMT

As @marcus_sommer is saying. No need for a loop. Just perform a join like this:

MyData:
Load MyField1, MyField2
From SomeTable;

Join (MyData) Load

CalDate, // date field numCalDate // numeric

From YourMasterCalendar;

Re: Loop to add a date field multiple times question

alexis — Mon, 27 Sep 2021 00:48:42 GMT

Thanks for the helpful answer Marcus

Re: Loop to add a date field multiple times question

alexis — Mon, 27 Sep 2021 00:49:22 GMT

Thanks Vegar