Loop to add a date field multiple times question

[alexis]

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

[marcus_sommer]

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

[Vegar · Accepted solution]

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;

[alexis]

Thanks for the helpful answer Marcus

[alexis]

Thanks Vegar