Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Creator III
Partner - Creator III

Loop to add a date field multiple times question

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

 

 

 

 

 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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;

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
MVP
MVP

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
Partner - Creator III
Partner - Creator III
Author

Thanks for the helpful answer Marcus

alexis
Partner - Creator III
Partner - Creator III
Author

Thanks Vegar