Loop through resident table to create dates

[jerryr125]

Hi - I have the following resident table :

ProductInfoTable

With the following Fields:

ProductID

ProductValue

Example data:

Product ID      Product Value

--------------    ---------------------

ABC                 1002

DEF                  2025

GHI                   3020

I would like to have a loop that will create a new table with one year of dates starting from the system date (today)  for each of the product ids.

So for example (new table)

Product ID

Product Value

Product Calendar Date

ProductInfoCalendarTable

ABC  1002  8/5/2020

ABC  1002 8/6/2020

ABC 1002 8/7/2020

DEF 2025 8/5/2020

DEF 2025 8/6/2020

DEF 2025 8/7/2020

GHI  3020 8/5/2020

GHI 3020 8/6/2020

GHI 3020 8/7/2020

and so on and so on....

any thoughts on how to do this ?

can weekend dates be excluded ?

Thanks - Jerry

[lironbaram]

something like this should work 

ProductInfoCalendarTable:
load ProductID,
     ProductValue,
     date(today()+iterno()-1) as ProductCalendarDate
residet ProductInfoTable
while date(today()+iterno()-1)<=date(addmonths(today(),12));

[jerryr125]

this worked perfect - thank you !

How can I remove the weekend days ? 

[lironbaram · Accepted solution]

hi add this load statement to the script 

after the initial build of the table 

ProductInfoCalendarTable2:
NoConcatenate Load *
resident ProductInfoCalendarTable
where weekday(ProductCalendarDate)<5;

drop table ProductInfoCalendarTable;

[jerryr125]

Thank you very much - appreciate it !