Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT 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