Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jerryr125
Professor-Ambassador
Professor-Ambassador

Loop through resident table to create dates

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

Labels (3)
1 Solution

Accepted Solutions
lironbaram
Partner
Partner

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;

 

View solution in original post

4 Replies
lironbaram
Partner
Partner

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
Professor-Ambassador
Professor-Ambassador
Author

this worked perfect - thank you !

How can I remove the weekend days ? 

lironbaram
Partner
Partner

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;

 

View solution in original post

jerryr125
Professor-Ambassador
Professor-Ambassador
Author

Thank you very much - appreciate it !