topic Loop through resident table to create dates in Connectivity & Data Prep

Loop through resident table to create dates

jerryr125 — Thu, 23 Dec 2021 13:19:46 GMT

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

Re: Loop through resident table to create dates

lironbaram — Wed, 05 Aug 2020 08:36:04 GMT

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));

Re: Loop through resident table to create dates

jerryr125 — Wed, 05 Aug 2020 21:02:00 GMT

this worked perfect - thank you !

How can I remove the weekend days ?

Re: Loop through resident table to create dates

lironbaram — Thu, 06 Aug 2020 05:21:59 GMT

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;

Re: Loop through resident table to create dates

jerryr125 — Fri, 07 Aug 2020 08:34:08 GMT

Thank you very much - appreciate it !